Learn how to create a simple select query, run the query , and save the results. Author Jen McBee will walk you through the steps of creating a query using two methods: the Query Wizard, and query Design View. Creating simple select queries is a skill tha
- You've created your database, your tables look great, now it's time to create some queries so that we can pull the information out and create great looking forms and reports. In this video we're going to learn how to create a simple query using the Query Wizard. We'll also create a query in design view. You'll be asked to demonstrate your skill at creating both simple select queries and more complicated queries on the Access 2013 MOS exam. So in this video we'll focus on using the Query Wizard to create our simple select queries.
I have our exercise file open, 0401, so let's go ahead and get started. The primary reason that we use queries is to make it easier to view, add, delete, or change data in your tables. Other reasons for using queries are to quickly find specific records by filtering on specific criteria. You can also run calculations and summarize your data in a query. We're going to start with just a simple select query and we'll work our way up to the more difficult queries.
We're going to create a simple query using the customer information table. Let's go ahead and open the table up so we can get an idea of what's in our table. Customer ID, name, contact information, and if I look down at the navigation bar I see we have 29 records. Okay, I'll go ahead and close this table. This time we'll use the Query Wizard. With the customer information table selected, I'll go to my Create tab and click on Query Wizard.
We'll do a simple query. I'll go ahead and click OK. Now because I had selected the customer table before I started with the Query Wizard, that table is selected. But notice that you do have the opportunity to select additional tables in this simple Query Wizard. I'd like to include all of the fields in the query so I'm gonna go ahead and click the double arrows in between the two fields. I'll go ahead and click next. I'll leave that default to show the detail of every record.
I now click next. This is where we can name our query. We're going to leave the default name Customer Information Query just as it is. We have two options, open the query to view the form, modify the query design. Now, on your MOS exam they may specifically ask you to either open it to view the information or they may specify that you just open it to modify the query design. So pay close attention to your instructions.
I'll go ahead and click Finish. Here's our query. Now, you might be thinking, "Well, it looks just like a table." Well, it does display it in data sheet view, which is nice because we're already comfortable working with tables, now we can see the information. It's actually a query, but it's in a data sheet view. Let's go into design view so you can see what Access did in the background for us. I'll right click on the tab and go to Design View. In design view we can see the table that we're pulling the information from up above.
And down below, this is called our query grid. Now, any field that has a check mark underneath it means that that will be displayed in the query results. If there was something that we did not want to include, we could just un-click the check box and it would hide that field. For instance, we probably don't need the ID field. Let's go ahead and remove the check mark, and then I'll run the query. I'll click the red exclamation point to run it and now we can see that that ID field is hidden away.
Okay, let's go ahead and close this query. You'll always be prompted to save the changes, I'll say yes. And let's create a query based on our order information, but this time we're gonna use the Query Design mode. I'll click on Query Design. Access wants me to tell it which table do I want to pull the information from. I'm gonna select the order information table. I'll click it to highlight it and then click add. I can now close my show table window. And I want to include all of the fields down below.
I can either click and drag them one at a time, or I can select several at the same time holding my shift key and bring them all down with one fail swoop. Let's go ahead and run the query. And there's our order information query. That's all there is to creating a simple select query. You now know two ways to create queries, using the Query Wizard and going right in to Query Design. On the Access 2013 MOS exam, you will probably be asked to demonstrate your knowledge of creating simple queries.
Be sure and practice creating and working with queries so you'll be very comfortable working with queries when you sit for your Access 2013 MOS exam.
Disclaimer: Microsoft does not produce, provide, or endorse this video training course.
The course first explores the MOS certification program and highlights its cost, format, and objectives. Jennifer then walks viewers through all of the Access exam areas in detail. She explains how to create and manage Access databases, build tables, join tables with relationships, and create queries, forms, and dynamic reports. She includes free practice files to follow along while learning about each topic. There are challenges to test your skills at the end of almost every chapter, and the full-length, 50-minute practice exam at the end of the course will ensure you're ready for the real exam.
- Identify the location of the search field in a table.
- Recall how to update identical information across multiple records.
- Explain how to create a report that pulls information from two tables.
- Explain the difference between a split form and a subform.
- Identify two methods used to create a calculated field in a query.
- Summarize how to display information in a particular order when creating a report.