MS Access —

Tour of a Query

The USA Employees query displays only the last name, first name, and country for employees from the USA.
The USA Employees query in Design view.

Webster's definition of a query is:

Que-ry

  1. A question; an inquiry.

  2. A doubt in the mind; a mental reservation.

  3. A notation, usually a question mark, calling attention to an item in order to question its validity or accuracy.

In its simplest form, a query in Access is no different than this definition well, the first one, anyway. Queries ask a question of the information in a table and then retrieve and display the results. For example, if you wanted to know which employees had worked for the company for more than five years, you could create a query to examine the contents of the HireDate field to find all the records in which the hire date is more than five years old. Access would retrieve the information that meets your criteria and display it in a datasheet.

Here's how to open and run a query:

  1. Click the Queries icon in the Objects bar.

    Access lists all the in the current database.

  2. Double-click the USA Employees query.

    Another way to open a query is to select the query and click Open.

    The USA Employees query opens in its own window, as shown in figure. This query asks the Employees table "Which employees are from the USA?" and then displays the results in a datasheet. The information displayed in the USA Employees query isn't a duplication of the data in the Employees tableit's just another way of looking at it.

    You have probably already noticed that the layout of the USA Employees query doesn't look any different than a table records appear in rows, fields appear in columns, and the record navigation buttons appear at bottom of the window. Some queries even allow you to add, edit, and delete records to and from the underlying tables (as is the case with this query).

    So how do queries work their magic? Let's take a "behind-the-scenes" look at the USA Employees query in Design view.

  3. Click the View button on the toolbar.

    Access displays the USA Employees query in Design view, as shown in figure. In Design view you can see a query's underlying tables, which fields are included in the query, and the criteria used to specify which records to display.

    Here you can see that the underlying table for this query is the Employees table, which appears in the upper portion of the Design view window. You can also see that three field namesLastName, FirstName, and Country appear in the design grid below. These are the fields that are included in the query. Notice that "USA" appears in the Criteria row below the Country field. The query displays only the records that meet the criteria entered in this row. This query filters only those employees whose Country field equals "USA."

    Let's try changing this query's criteria and see what happens...

  4. Select the "USA" text in the criteria box and replace it with UK.

    Now the query will display only employees from the UK. Let's return to Datasheet view and see the new query results. To switch back to Datasheet view, simply click the View button on the toolbar.

  5. Click the View button on the toolbar.

    Access displays the results of the query in Datasheet view. This time, instead of displaying employees from the USA, the query uses the new criteria and displays employees from the UK.

  6. Click on the Close button to close the USA Employees query.

    Because you made changes to the USA Employees query, a dialog box appears asking if you want to save your changes.

  7. Click No.

The USA Employees query used in this exercise is about as simple as queries can get. Queries can ask much more detailed and complex questions of tables, such as "What were the totals of last month's sales, by region?" or "Which sales representatives had higher than average sales?" or "Which customers have purchased our meteorite-protection coverage option for their car insurance?" For now though, you have a good understanding of what queries are and what they can do for you.

TO OPEN A QUERY:

  • CLICK THE QUERIES ICON IN THE OBJECTS BAR AND DOUBLE-CLICK THE QUERY OR SELECT THE QUERY AND CLICK OPEN.

TO DISPLAY A QUERY IN DESIGN VIEW:

  • OPEN THE QUERY AND CLICK THE VIEW BUTTON ON THE TOOLBAR.

OR...

  • CLICK THE QUERIES ICON IN THE OBJECTS BAR, SELECT THE QUERY, AND CLICK DESIGN.