A Quick Microsoft Access 97 Tutorial
By Charles W. Neville, © Charles W. Neville, March 2001
Verbatim copying and redistribution of this tutorial are permitted in any medium provided this notice and the copyright notice are preserved.

Chapter 4. Creating QBE Queries

Creating a New Query

The Current Product List query only displayed the Product ID and Product Name fields of the products which have not been discontinued. Let us create a new query which will also display the Supplier and Category fields. The fastest way to do this would be to open the Current Product List query in design view, and then alter the query. But instead, for practice, we shall create a new query from scratch.

Step 1. Be sure the database window is visible. Click on the Insert menu and then click on Query. (Alternate procedure: If the Queries tab is selected, click the New button to the right of the database window.)

Starting a new query
starting a new query

Step 2. Because there are choices to make as to how you will design the new query, you will be asked to choose the design method in the New Query dialog box.

Choosing how to design the new query -- the New Query dialog box
the new query dialog box

Step 3. Select Design View and press the OK button on the dialog box. This displays a new blank query in design view, and also presents you with the Show Table dialog box so you can choose which tables will be involved in the query. Select the Products table and press the Add button.

The new blank query in design view with the Show Table dialog box
the new blank query in design view with the Show Table dialog box displayed

Step 4. As the Products table will be the only table involved in the query for the time being, press the Close button on the Show Table dialog box. You are now ready to begin the real design work.

Ready to begin the real design work on the new query
ready to begin the real design work on the new query

Step 5. Be sure the cursor is in the first box of the Field row and press the selection down arrow button in the box to display the field choices for the Products table. Select ProductID.

Selecting the ProductID field for display
selecting the ProductID field for display

Step 6. Click on the second box of the Field row and press the selection down arrow button again to display the field choices for the Products table. Select ProductName. Continue in this fashion, with CategoryID in the third box and Discontinued in the fourth box.

The completed query
The completed query

Testing the New Query

You should always test a new query by running it.

Step 1. Click on the Query menu and then click Run (Alternate procedure: Click the ! button on the tool bar.)

Running the new query
running the new query

If all goes well, the new query will run rather quickly, and the following table of results will appear,

The result of running the new query
the result of running the new query

You should inspect all the records in the table of results. You will immediately notice a problem. There are 77 records instead of the expected 69, and some of the records have their Discontinued fields checked. We forgot to specify that the value of the Discontinued field should be No. We have to correct this error. This is why you should always test your queries.

We also forgot to uncheck the checkbox in the Discontinued field column. But this was fortunate because it helped us diagnose the problems with our query.

Correcting the New Query

Step 1. Click on the View menu and then click on Design View to change back to design view.

Changing back to design view
changing back to design view

Step 2. Enter No in the Criteria row of the field column.

The partially corrected query
the partially corrected query

Step 3. Run the partially corrected query to test it.

Testing the partially corrected query by running it
testing the partially corrected query by running it.

Inspect all the records in the table of results. Observe that there are the expected number of records, 69, and that none of the records have their Discontinued fields checked. Thus, the query appears to be correct, except for the display of the Discontinued field.

Step 4. Return to design view. Click on the checked check box check box in the Discontinued column to uncheck it. This completes the corrections to the query.

The fully corrected query
The fully corrected query

Step 5. Test the fully corrected query one more time by running it.

Testing the fully corrected query by running it
testing the fully corrected query by running it

Once more, carefully inspect the table of results. Observe that the query appears to be correct.

Saving the Fully Corrected Query

You need to save your new query for future use. To save the query, click on the File menu and then click Save. Save the query as CurrentProducts so you don't overwrite the Current Product List query which comes as part of your copy of the Northwind sample database. Save works just about the way Save works in your favorite word processor, so I won't present you with any screen shots. The only difference is that you save the query inside your copy of the Northwind database, so you don't get the usual Windows File Save dialog box. Instead, you just get a dialog box where you type in the name under which to save the query.

After you have saved the query, close it by clicking on the small x at the top right of the table of results on its title bar. (Be careful not to click on the x at the top right of the Microsoft Access title bar, or you will close Access completely.) Then inspect the Query tab and verify that your new query, CurrentProducts, is listed there. You can reopen and run the query any time you want by double clicking its name, CurrentProducts.

What's Next?

The next thing to do is to study Multi-table QBE Queries