Exploring Queries

You can locate specific information stored in a table, or in multiple tables, by creating a query specifying the parameters of the information you want to find. For example, you might want to locate all your out-of-state customers who have purchased gloves within the last three months. You could find this information by sorting, filtering, and cross-referencing table data, but that would be a difficult and time-consuming task. It is far simpler to create a query that returns all records in the Customers table with billing addresses not in your state, whose customer IDs map to records that appear in the Transactions table within the past quarter and include item IDs that map to records in the Inventory table that are classified as gloves. That might sound complicated, but the process of creating a query to return the results described in this example is quite simple.

Running a query (also called querying the database) displays a datasheet containing the records that fit your search criteria. You can use the query results as the basis for further analysis, create other Access objects (such as reports) from the results, or export the results to another format, such as an Excel spreadsheet or a Microsoft SharePoint list.

If you will want to locate records matching the search criteria at any time in the future, you can save the query, and run it again from the Queries section of the Navigation Pane. Each time you run a query, Access evaluates the records in the specified table (or tables) and displays the current subset of records that match the criteria you have defined.

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. You can easily create queries by using the Query wizard that 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 when you click Queries in the Navigation Pane.

See Also

For more information about queries, see Chapter 6, "Locating Specific Information."

In this exercise, you will explore two existing queries.

USE the 05_Queries database. This practice file is located in the Chapter01 subfolder under SBS_Access2007.

OPEN the 05_Queries database.

1.	In the Navigation Pane, click Queries. The database window displays all the queries that have been saved as part of this database.
2.	Right-click the Current Product List query, and then click Object Properties. Access displays the properties of the Current Product List query, including a description of its purpose. The icon shown on the General tab matches the icon shown for this query in the Navigation Pane, and is an indication of the query's type. The query type is also specified in the Properties dialog box: this is a Select Query.
3.	In the Properties dialog box, click Cancel.
4.	Right-click the Products by Category query, and then click Open. Access processes the query (commonly referred to as running the query) and produces a datasheet displaying the results. The record navigation bar indicates that 171 records are displayed; the database actually contains 189 records. To find out why 18 of the records are missing, you need to look at this query in Design view.
5.	On the View toolbar, click the Design View button. Design View Access displays the query in Design view. Two boxes in the top part of the query window list the fields in the tables this query is designed to work with. The query is formed in the design grid at the bottom of the query window. 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 finds all the records that don't have a value of Yes in that field (in other words, all the records that have not been discontinued).
6.	As an experiment, in the Criteria row of the Discontinued field, replace <> with =, and then on the Design contextual tab, in the Results group, click the Run button. Tip You can also run a query by switching to Datasheet view. This time, the query finds all the records that have been discontinued. The 18 discontinued products account for the difference between the number of records in the Products table and the number of records displayed by the original query.

CLOSE the Products By Category query without saving your changes, and then close the 05_Queries database.