Create a select or crosstab query (MDB)

 Note   The information in this topic applies only to a Microsoft Access database (.mdb).

Create a select query

ShowCreate a simple select query with a wizard

The Simple Select Query Wizard creates queries that retrieve data from the fields you specify in one or more tables or queries. If you want, the wizard can also sum, count, and average values for groups of records or all records, and it can calculate the minimum or maximum value in a field. However, you can't limit the records it retrieves by setting criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.).

  1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click Queries Button image under Objects, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Simple Query Wizard, and then click OK.

ShowIf the wizard doesn't start

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.

  1. Follow the directions in the wizard dialog boxes. In the last dialog box, you can choose either to run the query or to see the query's structure in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).

If the resulting query isn't exactly what you want, you can rerun the wizard or change the query in Design view.

ShowCreate a select query on my own

  1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click Queries Button image under Objects, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design View, and then click OK.
  3. In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
  4. Double-click the name of each object you want to add to the query, and then click Close.
  5. Add fields to the Field row in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), and if you want, specify criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) and a sort order.
  6. To view the query's results, click View Button image on the toolbar.

ShowFind duplicate records or field values in a table with a wizard

Using the Find Duplicates Query Wizard, you can create a select query (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.) to determine if there are duplicate records in a table. For example, you might search for duplicate values in an Address field to determine if you have duplicate records for the same supplier, or you might search for duplicate values in a City field to see which suppliers are in the same city.

  1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click Queries Button image under Objects, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Find Duplicates Query Wizard, and then click OK.

ShowIf the wizard doesn't start

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.

  1. Follow the directions in the wizard dialog boxes. If you don't choose to show fields in addition to those with duplicate values, the query results will sum the instances of each duplicate value. In the last dialog box, you can choose to run the query or see the query's structure in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).

If the resulting query isn't exactly what you want, you can rerun the wizard or change the query in Design view.

ShowFind unmatched records between tables with a wizard

Using the Find Unmatched Query Wizard, you can create a select query (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.) to find records in one table that don't have related records in another table. For example, you can find customers who don't have orders.

  1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click Queries Button image under Objects, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Find Unmatched Query Wizard, and then click OK.

ShowIf the wizard doesn't start

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.

  1. Follow the directions in the wizard dialog boxes. In the last dialog box, you can choose to run the query or see the query's structure in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).

If the resulting query isn't exactly what you want, you can rerun the wizard or change the query in Design view.

Create a crosstab query

ShowCreate a crosstab query with a wizard

  1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click Queries Button image under Objects, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Crosstab Query Wizard, and then click OK.

ShowIf the wizard doesn't start

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.

  1. Follow the directions in the wizard dialog boxes. In the last dialog box, you can choose to run the query or see the query's structure in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).

If the resulting query isn't exactly what you want, you can rerun the wizard or change the query in Design view.

ShowCreate a crosstab query on my own

  1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click Queries Button image under Objects, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design View, and then click OK.
  3. In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
  4. Double-click the name of each object you want to add to the query, and then click Close.
  5. Add fields to the Field row in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) and specify criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.).
  6. On the toolbar, click Query Type Button image, and then click Crosstab.
  7. For the field or fields whose values you want to appear as row headings, click the Crosstab row, and then click Row Heading.

You must leave the default Group By in the Total row for these fields.

  1. For the field whose values you want to appear as column headings, click the Crosstab row, and then click Column Heading. You can choose Column Heading for one field only, and you must leave Group By in the Total row for this field.

By default, the column headings are sorted in alphabetic or numeric order. If you want them to appear in a different order, or if you want to limit which column headings to display, set the query's ColumnHeadings property.

ShowHow?

You can change the order or limit the column headings in a crosstab query (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.). For example, in column headings containing the months of the year, you can display the months chronologically rather than alphabetically. Or, you can limit the columns to just January through June.

  1. Open the crosstab query in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. Click the background of query Design view, outside the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) and the field lists (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.).
  3. On the toolbar, click Properties Button image to display the query's property sheet.
  4. In the ColumnHeadings property box, enter the column headings you want to display, in the order in which you want to display them. Between the column headings, type a comma or the list separator for your country/region. (To find the list separator for your country/region, see the regional settings in Microsoft Windows Control Panel.)

The column headings you enter must exactly match the column headings in the query datasheet. For example, if a column heading in the datasheet is "USA," you must enter a column heading of "USA" — not "US." (After you press ENTER or move the pointer to a different location, Microsoft Access places quotation marks around each heading.)

  1. To view the query's results, click View Button image on the toolbar.

 Notes 

  1. For the field whose values you want to use in the cross-tabulation, click the Crosstab row, and then click Value.

Only one field can be set to Value.

  1. In the Total row for this field, click the type of aggregate function (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.) you want for the cross-tabulation (such as Sum, Avg, or Count).
  2. Do one of the following:

ShowSpecify criteria that limit row headings before the calculation is performed

For example, you might display sales totals for products in certain categories, such as meat and seafood.

ShowSpecify criteria that limit records before the row headings are grouped and before the cross-tabulation is performed

  1. Add the field you want to set criteria for to the design grid.
  2. Click Where in the Total cell.
  3. Leave the Crosstab cell blank.
  4. Enter an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) in the Criteria row.

The query results won't display fields that have Where in the Total row.

To view the query's results, click View Button image on the toolbar.

To stop the query after you start it, press CTRL+BREAK.

 Notes 

 
 
Applies to:
Access 2003