A Quick Microsoft Access 2000 Tutorial
By Charles W. Neville, © Charles W. Neville, March 2001
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 at the top of the database window.)
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.
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.
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.
Step 5. Be sure the cursor is in the first box of the Field row and press the button in the box to display the field choices for the Products table. Select ProductID.
Step 6. Click on the second box of the Field row and press the 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.
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.)
If all goes well, the new query will run rather quickly, and the following table of results will appear,
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.
Step 1. Click on the View menu and then click on Design View to change back to design view.
Step 2. Enter No in the Criteria row of the
Step 3. Run the partially corrected query to test 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 check box in the Discontinued column to uncheck it. This completes the corrections to the query.
Step 5. Test the fully corrected query one more time by running it.
Once more, carefully inspect the table of results. Observe that the query appears to be correct.
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.
The next thing to do is to study Multi-table QBE Queries