About select and crosstab queries

Select and crosstab queries are the two main ways you query a Microsoft Access database to retrieve just the data you want.

Select queries

What is a select query?

A select query is the most common type of query. You use it to:

• Retrieve data from one or more tables by using criteria you specify and then display the data in the order you want.

• Update records in the datasheet of a select query (with some restrictions).

• Group records and calculate sums, counts, averages, and other types of totals.

  Bring together data from multiple tables and sort it in a particular order.

  Perform calculations on groups of records.

Creating a select query

You create a query with a wizard or from scratch in query Design view. In Design view, you specify the data you want to work with by adding the tables or queries that contain the data, and then by filling in the design grid.

  Field lists show the fields in the tables or queries you add to your query.

  A join line tells Microsoft Access how data in one table or query is related to data in another table or query.

  You add fields to the design grid by dragging them to the field lists.

  The fields, sort order, and criteria you add to the design grid determine what you will see in the query's results.

Crosstab queries

What is a crosstab query?

You use crosstab queries to calculate and restructure data for easier analysis of your data. Crosstab queries calculate a sum, average, count, or other type of total for data that is grouped by two types of information — one down the left side of the datasheet and another across the top.

  This select query only groups the totals vertically by employee and category. This results in more records, making comparisons between different employees' totals more difficult.

  A crosstab query displays the same information, but groups it both horizontally and vertically so the datasheet is more compact and easier to analyze.

Creating a crosstab query

You create a crosstab query with a wizard or from scratch in query Design view. In the design grid, you specify which field's values will become column headings, which field's values will become row headings, and which field's values to sum, average, count, or otherwise calculate.

  The settings in these rows determine how data is shown.

  This setting displays the field's values as row headings.

  This setting displays the field's values as column headings.

  These settings display the total orders.

Let Microsoft Access create a select or crosstab query for you

Access can often create a query for you so you don't have to design one from scratch.

• To create a query to use as the basis of a form, report, or data access page, try the form, report, or data access page wizards. They create the form, report or data access page, and if it's based on more than one table, they also create its underlying SQL statement. If you want, you can save the SQL statement as a query.
• To easily create queries that you want to run independently or base multiple forms, reports, and data access pages on, try one of the query wizards. Query wizards do all the basic work for you after you provide answers to a series of questions. Even if you've created many queries, you may want to use a wizard to quickly design the query. Then you can switch to Design view to customize it.
• To create queries from filters you created using Filter By Form, Filter By Selection, or Filter For Input, save the filter as a query.

If none of these methods satisfies your needs, you can create the query from scratch in query Design view.