Creating queries with the Simple Query Wizard is the easiest way to create and deploy a query in Microsoft Access. Because queries save time and make your workflow more efficient, it's important to understand how to create and utilize them. This online course will teach you how to make, open, and use a query using the Simple Query Wizard.
As with many of the objects in Access, the easiest way to get started with queries is to have the wizard walk us through some of the steps. Before we get started, let's have a goal in mind. Let's first take a look at the existing data tables. Our EmployeePhone list is setup as a table that is related to the Employees table. If we take a look, we can see an EmployeeID field here that is the foreign key that matches the primary key in the Employees table. This phone table tells me that the first three records here all belong to the same employee, number 635.
But it doesn't tell me who that employee is. Instead of looking this information up manually, we can use a query to reassemble it for us. Let's go ahead and close this EmployeesPhone table. I am going to go to the Create Tab here and we'll create a new query using the Query Wizard. I will start the wizard. We're going to do a simple query, so go ahead and say OK. And the next screen that opens up asks us which tables are we going to use as the basis for our query. We had already selected the EmployeesPhone table over here in the navigation pane, so that's the first table that it suggests.
I am going to actually use the Employees table first, because I want my names to appear at the beginning. I will choose FirstName, and I am going to use this arrow button to move it over from the Available Field side to Selected Fields. I will do the same thing with LastName. Make sure it's highlighted, and then press this arrow to move it over to select it. Now if I wanted to move all of the fields at once, I could use this double-arrow. If I wanted to undo that and move everything back, I can use this double-arrow to move them all back from selected to available. And if I just want to move one field back, I would use this button here to move it back to the other side.
So I am going to make sure that I have FirstName and LastName. We also want to pull in the related records from the phone table. So I will go back up to the dropdown and choose the EmployeePhone table. There, I want to know the phone number, and the type of number that it is. Go ahead and say Next. Access asks us if we want to show details or a summary. We want to show every field of every record, so go ahead and say Next, and we can go ahead, and give it a name here. Employees Query doesn't really describe what this is, so I am going to call this EmployeePhoneNumbers.
Finally, we have the option to choose whether we want to open the query, or go into Design View to continue editing. I am going to choose to 'Open the query to view the information', and press Finish. Access returns the query results, and we can see now that the first three phone numbers all belong to the same person, Zachary Tate. Now additional summary options are available when numeric data is included in the query. Let's get a query that summarizes our data instead of displaying every record. I am going to close the results of our EmployeePhoneNumbers query. If I needed to get it back, you will notice that it's here in the Navigation pane.
This time I am going to create a query that takes a look at our products, and our orders. I've got two new tables here. The Products table, if I open that, you will see that it includes all of the products that NoObstacles carries. I will go ahead and close that table. The Orders table has a list of ID numbers that match customer IDs to the product IDs. It has a listing of the dates that the product was ordered, and the quantity that the products was ordered. Go ahead and close the Orders table here. We're going to create a query that summarizes that information.
We'll go back up to the Query Wizard, a simple query, go ahead and say OK. This time I am going to choose the Products Table, and from that, I want to know which products were ordered. From the Orders table, I want to get the quantity that has been ordered from every customer. We'll add that to the selected fields, go ahead and say Next. This time, instead of showing every field of every record, I am going to get some summaries. We'll click the Summary and then Summary Options.
I will choose to add up all of the quantities of every product, so that I can get a listing of the total ordered for each product. Go ahead and say OK, and Next. Finally, we can give this a name. I am going to call it TotalProductsOrdered. Once again, I am going to choose to open the query to view the information, and we'll press Finish. Now I've got a listing of all the products that we sell and the total number of products that have been ordered throughout the company. The Query Wizard can help you quickly get up to speed constructing some basic queries.
For even more control and options, we need to take a look at the query design environment and we'll do that in the next movie.
Author
Released
2/8/2013- Understanding table structures and relationships
- Setting primary and foreign keys
- Establishing relationships and maintaining referential integrity
- Sorting and filtering data
- Building queries with constraints and criteria
- Editing table data with queries
- Generating forms from tables
- Adding form controls
- Creating reports with totals and labels
- Embedding macros in buttons
- Repairing your database
- Protecting databases with passwords
Skill Level Beginner
Duration
Views
Related Courses
-
Excel 2013 Essential Training
with Dennis Taylor6h 32m Intermediate -
Setting Up a Database in Excel 2013
with Dennis Taylor49m 17s Intermediate -
Access 2013: Tips & Tricks
with Adam Wilbert2h 50m Intermediate
-
Introduction
-
Welcome1m 1s
-
-
1. Getting Started
-
Key database concepts4m 21s
-
Creating the database file3m 25s
-
Digging in to Ribbon tabs3m 39s
-
Using the Navigation pane3m 36s
-
Taking backups47s
-
Accessing help1m 8s
-
-
2. Creating Tables
-
Importing tables4m 57s
-
3. Setting Field Properties
-
Setting the default value2m 15s
-
Creating lookup fields4m 29s
-
4. Organizing Records
-
Formatting columns2m 52s
-
Sorting table data3m 18s
-
Filtering table data2m 21s
-
-
5. Using Queries
-
Understanding queries2m 2s
-
Building expressions5m 23s
-
-
6. Working with Specialty Queries
-
Using update queries3m 21s
-
Using make table queries2m 57s
-
Using delete queries2m 48s
-
Using append queries2m 49s
-
-
7. Creating Forms
-
Generating forms from tables3m 10s
-
Using the Form Wizard2m 38s
-
8. Designing Forms
-
Setting data sources5m 39s
-
Understanding input boxes3m 31s
-
Recording navigation2m 36s
-
Adding buttons4m 18s
-
Assigning tab order4m 5s
-
Creating a navigation form3m 36s
-
9. Creating Reports
-
Introducing reports2m 15s
-
Using the Report Wizard5m 9s
-
Creating calculated totals3m 32s
-
Creating labels4m 18s
-
Adjusting print settings2m 58s
-
-
10. Working with Macros
-
Creating macros5m 3s
-
-
11. Integrating Access with the Office Suite
-
Emailing with macros4m 4s
-
12. Maintaining the Database
-
Documenting your work4m 41s
-
Creating custom Ribbon tabs3m 35s
-
Conclusion
-
Next steps1m 6s
-
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Creating queries with the Simple Query Wizard