Understanding the Different Types of Queries

Access offers several types of queries that help you retrieve the information you need ”select queries, crosstab queries, action queries, and parameter queries.

• A select query retrieves and displays records in the Table window in Datasheet view.

• A crosstab query displays summarized values (sums, counts, and averages) from one field in a table, and groups them by one set of fields listed down the left side of the datasheet and by another set of fields listed across the top of the datasheet.

• An action query performs operations on the records that match your criteria. There are four kinds of action queries that you can perform on one or more tables: delete queries delete matching records; update queries make changes to matching records; append queries add new records to the end of a table; and make-table queries create new tables based on matching records.

• A parameter query allows you to prompt for a single piece of information to use as selection criteria in the query. For example, instead of creating separate queries to retrieve customer information for each state in which you do business, you could create a parameter query that prompts the user to enter the name of a state, and then continues to retrieve those specific records from that state.

Creating Queries in Access

As with most database objects you create in Access, there are several ways to create a query. You can create a query from scratch or use a wizard to guide you through the process of creating a query.

With the Query Wizard, Access helps you create a simple query to retrieve the records you want. All queries you create and save are listed on the Queries tab in the Database window. You can then double-click a query to run it and display the results. When you run a select query, the query results show only the selected fields for each record in the table that matches your selection criteria. Of course, once you have completed a query, you can further customize it in Design view. As always, you can begin creating your query in Design view without using the wizard at all. Queries are not limited to a single table. Your queries can encompass multiple tables as long as the database includes a field or fields that relate the tables to each other.