Get started with queries

3/18/2020

Use queries to answer business or other questions based on data and to quickly and easily make bulk updates or to delete information from the database.

What are queries?

In Access, queries are like questions that you ask to find related, even very specific, information in your database.

In queries, you sometimes use data from just one table and other times from more than one. For example, you might want to just find a contact’s phone number, which requires a simple query of a phone number field for a specific person in a contacts table. Or you might combine data from more than one table, such as customer information and order information, to see what a customer has ordered.

In Access, a query is a database object. It doesn’t store data. Instead, it displays data that is stored in tables and it makes that data available for you to work with. A query might show data from one or more tables, from other queries, or from a combination of the two. For example:

• View data with a select query. Find and view information from one or more tables by specifying criteria that the data must meet and itemizing which values to display. For example, you might ask to see all products created by Tailspin Toys.
• Enter criteria and search on the fly. Create reusable queries that always ask you for search criteria. For example, you might create a query that asks the user for the name of the supplier to search for and then displays all the products acquired from that supplier.
• Update or add data based on query. Query for data and then use the results to automatically enter or update information. For example, if Tailspin Toys has added “TT” to the beginning of the names of all its products, search for all of the company’s products and then update the product names so that each entry begins with “TT”—all through an update query.
• Delete data based on a query. Find information or records and then delete them. For example, if Tailspin Toys goes out of business and the company’s products are no longer available for sale, find all of their products and automatically delete them from the relevant tables.

Use the Query Wizard to create a select query that displays information in Datasheet View.

Note: Some design elements aren’t available when you use the wizard. If you need to, modify the query in Design View after you create it.

Note:  Before you create a query, be sure you’ve established the relationships between tables in your database, because they are used when querying your data. To learn more, see Get started with table relationships and related modules in this training.

Create a query

1. Select Create > Query Wizard.
2. Select Simple Query Wizard, and then select OK.
3. Select the table or query that contains the field, and then add the field to the Selected Fields list. When you’re finished, select Next.

Add as many fields as you want from as many tables as you want.

If you added a number field

If you added any number fields, the wizard asks whether you want the query to return details or summary data. Do one of the following:

• • If you want to see individual records, select Details, and then Next. Skip ahead to step 4.
  • If you want to see summarized numeric data, such as averages, select Summary and then select Summary Options. Specify which fields you want to summarize and how you want to summarize the data. Only number fields are listed. For each number field, select a function:
    • Sum   The query returns the sum of all the values of the field.
    • Avg    The query returns the average of the values of the field.
    • Min    The query returns the smallest value of the field.
    • Max   The query returns the largest value of the field.
  • If you want the query results to include a count of the records in a data source, select the Count records in table name for that table.

If you added a date/time field

The Query Wizard asks you how you would like to group the date values. For example, suppose you added a number field, such as price, and a date/time field, such as transaction time, to the query, and then specified in the Summary Options dialog box that you want to see the average value of the price field. Because you included a date/time field, you could calculate summary values for each unique date and time value, for each day, month, quarter, or year.

Select the time period that you want to use to group the date and time values, and then select Next.

Note: In Design View, you have more options for grouping date and time values.

4. Give the query a title.
5. Specify whether you want to open the query (in Datasheet View) or modify the query (in Design View). Then, select Finish.