One way you can locate information in an Access database is to create queries. You use queries to locate information so that you can view, change, or analyze it in various ways. You can also use the results of queries as the basis for other Access objects.
A query is essentially a question. For example, you might ask, "Which records in the Customer table have the value 98052 in the Postal Code field?" When you run a query (the equivalent of asking a question), Access looks at all the records in the table or tables you have specified, finds those that match the criteria you have defined, and displays them in a datasheet.
For Access to be able to answer your questions, you have to structure queries in a specific way. Each type of question has a corresponding type of query. The primary query types are select, crosstab, and parameter. Less common types are action, AutoLookup, and SQL (Structured Query Language). Access includes wizards that guide you through the creation of the common queries; less common ones have to be created by hand in a design grid in Design view. Here’s what a typical query looks like:
At the top of this query window are four small windows listing the fields in the four tables that will be included in this query. The lines connecting the tables indicate that they are related by virtue of common fields. The first row of the grid contains the names of the fields to be included in the query, and the second row shows which table each field belongs to. The third row (labeled Total) performs calculations on the field values, and the fourth row indicates whether the query results will be sorted on this field. A check mark in the check box in the fifth row (labeled Show) means that the field will be displayed in the results datasheet. (If the check box isn’t selected, the field can be used in determining the query results, but it won’t be displayed.) The sixth row (labeled Criteria) contains criteria that determine which records will be displayed, and the seventh row (labeled or) sets up alternate criteria.
Don’t worry if this all sounds a bit complicated at the moment. When you approach queries logically, they soon begin to make perfect sense. And don’t worry if they sound like a lot of work. The Query Wizard is available to help you structure the query, and if you create a query that you are likely to run more than once, you can save it. It then becomes part of the database and is displayed in the database window when you click Queries on the Objects bar.
In this exercise, you will explore a few of the queries that have already been defined and saved in the GardenCo database.
USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My DocumentsMicrosoft PressAccess 2003 SBSKnowAccessQueries folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
On the Objects bar, click Queries.
The database window displays all the queries that have been saved as part of the GardenCo database.
Double-click the title bar of the database window to maximize the window.
The top two entries in this window are commands for creating queries. The remaining entries are queries that have already been created.
Click the Details button.
The description of each query explains its purpose. The icon in the Name column is an indication of the query’s type, as is the information in the Type column, which you can see by scrolling the window to the right.
Click the Restore Window button on the menu bar (not the title bar).
The database window is restored to its original size.
Open the Products by Category query in Datasheet view by selecting it and clicking the Open button at the top of the database window.
When you open the query, Access processes it (described as running a query) and produces a datasheet that displays the results.
The Navigation bar tells you that 171 records are displayed. The Products table contains 189 records. To find out why 18 of the records are missing, you need to look at this query in Design view.
On the toolbar, click the View button to view the query in Design view.
In the top part of the query window, two boxes list the fields of the tables this query is designed to work with. The bottom part is the design grid, where the query is formed. Each column of the grid can refer to one field from one of the tables above. Notice that <> Yes (not equal to Yes) has been entered in the Criteria row for the Discontinued field. This query therefore finds all the records that don’t have a value of Yes in that field (in other words, that have not been discontinued).
As an experiment, in the Criteria row of the Discontinued field, select the text <>Yes, type
=Yes, and then click the Run button on the toolbar.The query is changed so that it now finds all the records that have a value of Yes in the Discontinued field (in other words, that have been discontinued).
The 18 discontinued products account for the difference in the number of records in the Products table and the number of records displayed by the original query.
Close the query window, clicking No when prompted to save the design changes.
CLOSE the GardenCo database.