Types of Queries
The many different types of queries that Access provides give you many different ways to select and view specific data in your database. You choose the type of query, choose fields you want to see, and define criteria to limit the data shown as necessary.
The following list includes the types of queries available in Access:
- Advanced Filter/Sort: The simplest kind of query, Advanced Filter/Sort allows you to find and sort information from a single table in the database. This option is available from any datasheet by clicking Advanced in the Sort & Filter group of the Home tab on the Ribbon and choosing Advanced Filter/Sort.
- Select Query: A select query selects the data you want from one or more tables and displays the data in the order in which you want it displayed. A select query can include criteria that tell Access to filter records and display only some of them. Select queries that display individual records are called detail queries; those that summarize records are called Summary or Totals queries.
- Totals or Summary Query: These queries are a subset of select queries, but they allow you to calculate a sum or some other aggregate (such as an average) rather than displaying each individual record.
- Parameter Query: A query that asks you for one or more pieces of information before displaying the datasheet.
- AutoLookup Query: A query that fills in information for you. (AutoLookup queries are covered later in this tutorial.)
- Action Query: Action queries change your data based on some set of criteria. Action queries can delete records, update data, append data from one or more tables to another table, and make a new table.
- Crosstab Query: Most tables in Access, including ones generated by queries, have records down the side and field names across the top. Crosstab queries produce tables with the values from one field down the side and values from another field across the top of the table. A crosstab query performs a calculation - it sums, averages, or counts data that is categorized in two ways, as defined by the row and column labels.
Select queries are the most common type of queries used in Access. In fact, select queries are the most general type of query, and all the other query types add features to select queries. When you define a select query, you use the design grid to select which fields and records to display in the new datasheet. The skills you use to define select queries are also used to define the other types of queries.
In this tutorial:
- Creating Select Queries
- Types of Queries
- Creating a Query in Design View
- Creating a Query with the Simple Query Wizard
- Viewing Your Query
- Tips for Creating a Query
- Editing a Query
- Sorting a query
- Changing the format of a query field
- Limiting Records with Criteria Expressions
- Using dates, times, text, and values in criteria
- Using multiple criteria
- Using lookup fields in criteria
- Working with Multiple Related Tables
- Working with Query Datasheets
- Saving Queries