applied tips : april 2008

microsoft access : queries 101

A query sifts through thousands of records, displaying exactly what you need to know.

In Access, a query pulls together information from tables and other queries and displays only the fields and information you want to see. Queries discover things like what the average enrollment was for a class you offer or which customers bought the most Mountain Dew. Queries can also make widespread changes to the records in your database without wearing out your mouse and keyboard! For example, a delete query can automatically delete a whole bunch of records that meet your criteria.

The most common kind of query is a select query, which simply displays information. You can create a select query based on tables, other queries or both. There are also two different ways to create a select query: in Design View or using a wizard. Creating a query in Design View is almost always faster and more straightforward, so that’s the method we’ll discuss in this month’s article.

Creating a query

To create a query in Design View click the Queries icon in the Objects bar (or the Queries tab in Access 97), click the New button, make sure Design View is selected and click OK.

The Query design window and Show Table dialog box will appear. You have to select the tables and/or queries you want to pull information from. Simply select the table you want to add to the query and click Add—or just double-click the table. To add a query, click the Queries tab, select the query you want to add and click Add.

You will often need to look at information from several different tables. Since Access is a relational database, it’s easy to establish a relationship between two or more tables and look at the information that goes together.  Just like it sounds, a multiple‑table query blends together information from two or more related tables. To create a multiple‑table query simply continue adding the required tables or queries to your query. Click Close when you’ve finished adding the tables and/or queries to your new query.

If necessary, join related tables by dragging the related field from one table to the related field in the other table.

Add the fields you want to see

Next you need to specify which fields you want to use in your query. Notice that the query design window is split in two. The top half contains field lists of the tables and queries you selected, while the bottom half contains a design grid where the fields and information appear. You can add the fields you want to appear in your query to the design grid by double-clicking the field on the field list, or by clicking and dragging the field down to the design grid yourself.

Specifying criteria

Now you need to specify the criteria to display only those records that meet a certain condition. For example, you might want to display only customers from the 209 area code.

You specify which records you want to retrieve by entering the criteria and operators shown below into the criteria rows of the Query Design View.

Entering criteria on several fields in the same criteria row creates an AND statement. For example, which customers are younger than 25 AND are from Sacramento?

Entering criteria on several fields in different criteria creates an OR statement. For example, which users are younger than 25 OR are from London?

Sorting things out

Last, you may want your query to sort its information and present it in an ordered, easy-to-read display. Simply add the field(s) you want to use to sort the query to the design grid and click the Sort box list arrow for the first field you want to use to sort the query and select a sort order.

schedule a class or get consulting on access

Request an on-site Access class from Applied Office or get some helpful consulting. Learn more here

upcoming classes on access

Classes on Microsoft Access are scheduled at University of the Pacific, and you can attend!  Review the Upcoming Classes for more information.

quick reference card

Get the Quick Reference Card on Microsoft Access! Download it for free and print it on your own printer. You might even want to laminate it.