How to Build Access Queries

• Create a query with the Access Query Wizard.
• Create your own query from scratch.

How to Use the Access Query Wizard

If you need to create only a basic query, such as the four types of queries described below, you can use the Query Wizard. To use the Query Wizard, click on the Query Wizard icon at Create > Other and then follow the prompts in the dialog box that pops up to create your query. You’ll be able to choose from among four different types of queries:

• Simple query: The most basic query, a simple query lets you specify which fields to display from a single table. For instance, if you have a customer contact info table with dozens of fields, you might create a simple query that returns the two most important fields, such as the customer’s name and phone number.
• Crosstab query: This query works much like a simple query but lets you perform a basic calculation, such as finding an average, on one or more fields in the table.
• Find duplicates query: This query finds and displays duplicate data in the fields of a table or another query.
• Find unmatched query: Finds and displays rows in one table that do not match the data in the rows of another table. This is the only Query Wizard query that lets you work with data in more than one table.

How to Create Queries from Scratch

1. Create a New Query

To start the process of building your own query, click on the Query Design icon at Create > Other. When you click on it, Access will switch to Design view, and a Design tab will appear in the Ribbon that contains all of the commands you need to build your query.

2. Choose a Type of Query

• Select: Used to retrieve and display data from tables or other queries that you’ve already built and run
• Make Table: Selects records from one or more tables in a database and saves those records in a new table
• Append: Adds records to existing tables with data that you supply or import
• Update: Makes changes to records in existing tables
• Crosstab: Used to analyze data based on two sets of values—one appears down the left side of the table and one across the top of the table
• Delete: Removes specific data or entire fields or records from existing tables
• Union: Combines and displays the data returned by one or more select queries
• Pass-Through: Creates queries that run directly on ODBC database servers, such as SQL databases
• Data Definition: Used to make major changes to database objects, such as deleting entire tables

3. Select Tables or Existing Queries

1. To specify which tables a query should run on, click on the Show Table icon at Design > Query Setup.
2. In the Show Table dialog box that pops up, select the tables and/or queries to add by highlighting them and clicking Add, and then click Close.

4. Specify Fields

• Top half: Includes a small window for each of the tables or queries you chose to run the new query on
• Bottom half: Shows a design grid with a list of categories for the query that you need to select in order to tell Access exactly what the query should do

1. Select the table or query you’d like to work on by clicking on the blue bar at the top of its window in the top half of the document pane.
2. Specify the field(s) in each table or query that you’d like to run the query on by clicking on the name of the field in the table’s or query’s window. To use all the fields, click on the asterisk at the top of the list. Each field you click on will subsequently appear in the design grid.

5. Specify the Calculation for Each Field

1. Click on the Totals icon at Create > Show/Hide.
2. In the design grid, click on the field for which you’d like to specify a calculation.
3. Choose a calculation type, such as:
   1. Sum: Totals the values in a field
   2. Average: Calculates the field’s average value
   3. Min: Returns the lowest value in the field
   4. Max: Returns the highest value in the field
   5. Count: Calculates the number of non-null (not blank) values in a field
   6. StDev: Calculates the field’s standard deviation
   7. Var: Calculates the variance of values in a field
   8. Where: Specifies fields on which the query will not run (these fields are not shown in the query results)

6. Specify Criteria for Fields

1. Click on the field’s Criteria cell in the design grid.
2. Click on the Builder icon at Design > Query Setup.
3. In the Expression Builder dialog box that pops up, build your expression using the arithmetic words and symbols provided, such as “=,” “and,” “or,” and “not.”
4. Once you’ve built your expression, click OK. Access will insert the expression into the Criteria cell you clicked on in step 1.

7. Sort the Output of Certain Fields

8. Run the Query

To run a query, click on the Run icon at Design > Results. Access will perform the query’s actions and display results in a new document tab (if applicable).