Try it! Transcript



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.

Want more?

Create and run an update query

Excel training

Outlook training

Once your Access database has tables and relationships, it's time to design and build some queries, which are essential components of any database.

In this video, you'll learn the basic process for creating and running a query, get an introduction to several types of queries, create a query in the Query Wizard, and modify a query in the Query Designer.

Queries are literally the questions you ask of your data. In fact, asking the question you want answered can be a good place to start.

For example, "How many orders did TailSpin Toys place last month?"

The language in that question points you to the Orders table, and from there you select the fields you want to see, build the query, and then run it.

That's the basic flow for most queries. You ask your question and then you pick a data source—the tables, or even other query results which contain the data—that will answer that question.

You select the fields you want to see in the result, which is also called the "data set" by the way, and add any criteria.

In this example, the criteria filter the results to TailSpin Toys for the month of May.

You can use queries for more than just answering questions. Queries are like a multi-tool or combination pliers—they can be one of the handiest tools in any database because they do so much.

For example, queries can provide data for forms and reports, and they can also help you make bulk changes to your data.

Now, you can build several types of queries in any database. Let's take a look by selecting Create, then Query Design.

We'll close the Show Table dialog box for now.

These are the types of queries you can create.

A Select query is the most common. It's the type that helps you get answers out of your data.

Use a Make Table query to select records from an existing table and save them as a new table. This can be handy, for example, if you import a big Excel file and you want to break some of that data into separate tables.

You run these next three queries against existing data.

Append queries add data to existing tables, ...

Update queries change existing data, ...

and Delete queries remove data.

You use a Crosstab query when you need to display summary data such as averages or sums.

For example, if you need sales by region, use a Crosstab query.

Here's what a Crosstab query looks like in the Query Designer.

And the results look like this. You can see this one returns sales data per quarter.

Use a Union query to combine multiple Select queries into a single result. Do this when you need to see data from unrelated sources.

Use a Pass-Through query to connect to a large database such as, say, Microsoft SQL server, and process queries on that database.

When you do this, Access becomes the front-end —essentially a set of tools that help you run the larger database.

Finally, use a Data Definition query to create or modify tables and indexes. The thing is, Access provides so many other ways to create tables and change indexes that you'll probably never actually use this type of query.

Now let's create a Select query, the type of query you'll use the most.

To build it, let's select the Create tab and then select the Query Wizard.

If you're new to queries, this is a nice place to start.

Select Simple Query Wizard, and select OK.

Select the Phone Numbers table, and then select the Customer and Phone Number fields.

Select Next.

Change the query title if you want something more descriptive.

Select that you want to display the results, ....

… and then select Finish.

Here's the result.

You might have a couple of questions at this point, like "What kind of query did we just create?" ...

And, "How do we know whether we're calling someone's office phone or their fax machine?"

You can answer those questions by adding some data to the query.

To modify your query, let's open the Query Designer. Select Home, then View, then Design View.

The Query Designer shows you the data source—in this case it's a table but it can also be a query. You also see the field involved in the query.

The ribbon indicates the TYPE of query, and in this case, you can see that it's a Select query.

To add the Type field and make the phone list easier to use, you just drag it.

To see the results, on the Design tab, select Run.

Now, although using the Query Wizard is quick and easy, you can't do everything with it. For example, you can't add formulas by using the Query Wizard.

So for increased flexibility and to make whatever changes you might need, use the Query Designer.

So now you've learned the basics for creating and running a query by using the Query Wizard and the Query Designer. Using queries helps you analyze your data and get the answers you need.