Using the Query Wizard and Query Designer in Access 2013

Getting Set Up

I’m going to use the Desktop Asset tracking database example built into Access 2013. Once loaded, I populated some data into the Contacts and Asset list forms. As you can see in Figure 1, I have a small set of data tracking computers and a few monitors. We’ll build some queries in this guide to show you exactly how queries work. 

Query Wizard

The Query Wizard allows you to create basic queries that let you extract sets of data from a table. In the first example we’ll run a ‘Simple’ query. Simple queries have no logic to the query – just extract everything contained in a table for a number of selected fields.

Example 1: Select all data contained in the Assets table using the Item, Purchase Price and Acquired Date fields.

1. With your database open, click the Create tab and click Query Wizard.
2. The query wizard will open. Select Simple Query and click OK.
3. Make sure the Table:Assets table is selected.
4. Select the Item, Purchase Price and Acquired Date fields (Figure 2). Click Next.
5. On the Detail\Summary page keep Detail selected. Click Next.
6. Give your query a name and click Finish.

As you can see in Figure 3, we have a new table listing only the fields we selected from the query.

For the next query we’ll create a crosstab query. This type of query will help summarize data by displaying it in a compact form. 

Example 2: Display the total cost of assets (purchase price) by person and category.

1. With your database open, click the Create tab and click Query Wizard.
2. Select Crosstab Query Wizard and click OK.
3. Select Table:Assets and click Next.
4. Now we need to select the field that will be grouped in rows. In this case we want to display the owners. Select Owner and click Next.
5. For column heading we want the Category. Select Category and click Next.
6. Now we need to tell the query which field to summarize. In this case we’ll select Purchase Price and also change the function to Sum. Be sure the ‘Yes, include row sums’ is checked (Figure 4) and click Next.
7. Give your query a name and click Finish. 

As you can see in Figure 5, we now see rows of our contacts listed with the total amount of purchased assets they have. 

Query Designer

Although the query wizard is handy for basic queries you may run into situations where you need a bit more control over what is being queried. This is where the Query Designer comes in. To find this, click the Create tab and select Query Design.

Example 3: Query the assets table showing Item, Category, Acquired Date, Owner and Purchase Price for only items in Good condition. Sort this by the Owners name. Ask the user for input as to which Category is displayed.

1. Open the Query Design tool.
2. You are first asked what tables to open – select the Assets table and click Add. Click close when done.
3. Along the bottom is where you’ll build the query. Select each field to be used in the query.
4. Since we want to exclude conditions other than ‘(2) Good’ we can use the word ‘NOT’ in the criteria row for the Condition column. Type “Not ‘(2) Good’”. A list of query criteria examples can be found on the Microsoft Office support site.
5. We don’t need the Condition column to show up in the final query so we can deselect the ‘Show’ checkbox for that column.
6. We will use the Sort row for the Owner column to do Descending sort on this field.
7. Finally, we will use a Query Parameter to ask the end user which Category they want displayed. In the Criteria field for the Category, type “[Enter a category:]” – without the quotes.
8. Your final query design should look like Figure 6.

You can run the query by clicking the View button. As you can see in Figure 6 you will be prompted to enter a category. Type in “(1) Category” and you will see a single result. Type in “(2) Category” and you’ll see different results (Figure 7).

There is a lot of power in Access queries that we didn’t even start to scratch the surface of. I hope this piques your curiosity and leads you to continue learning about Access and queries. Data manipulation and reporting are hot skills to have right now so get out there and start practicing!