Help: Query Basics

Queries are the work horses of a database system: Their purpose in an Access application is to display data in related tables, and to filter that data as needed. This help file will give the inexperienced user some tips on how to manipulate queries that have been created, and some criterion patterns will be listed. Query criteria used to filter the records can be changed as needed. So, a report based on a query can display different data based on the criteria set in the query for the current report.

Use the Wizard to Build a New Query
In the Access window,
  1. Click the CREATE tab near the top.
  2. Click Query Wizard on the CREATE tab.
  3. Select Simple Query Wizard, then click OK.
  4. Click Down Arrow to open drop-down list box. Select a table or query to supply data.
  5. Select a field name and click the > button.
  6. Select the Detail radio button.
  7. Name the Query and Open it.
And, that's it!

Access Home Ribbon
1. Click the CREATE tab on the Ribbon menu.

Query Wizard on CREATE tab
2. Click the Query Wizard on the Ribbon menu.

Choose Simple Query Wizard
3. Select Simple Query Wizard, then click OK.
Open Drop-down list Box
4. Click Down Arrow to open drop-down list box. Select a table or query to supply data.

Select some fields
5. Select a field, click > button. Repeat.
Selected Fields
Some fields have been selected.

Choose Detail radio button
6. Select Detail radio button.
Name the Query & Open it
Name the Query & Open it.

That's all there is to creating the simple query with the wizard. If you wish to use the features of the relational database system, you need to build your queries using the Query Design screen.

Query By Design
The other way to build a query is using the Query Design screen. With the CREATE Ribbon in focus,
  1. Click Query Design on the CREATE tab. The Query Design window pops up with the Show Table dialog box front and center.
  2. In the Show Table window, select a table or query and click Add. You add more than one existing table or query. In order for them to work together they must have one field that contains identical information regardless of the field names. e.g. In the Members application tables that relate to individuals each have an ID field with the name Mem_ID or similar name. Each of those fields has the same data as the Members table Mem_ID field. Those tables will relate well in a query.
  3. Close the Show Tables window.
  4. Drag a field name down to the Field row in the lower half of the query design window. Continue to drag field names until you have the fields you wish to display in this query. My sample query uses tbl-Members and tbl-Lookup_Zip.
  5. Click the datasheet icon on the left edge of the ribbon to run the query and see the results. It will return the table data in the fields included in your new query.
  6. Click the Floppy Disk (Save) icon. Enter your query name in the save dialog box and click OK.
And, that's it!

All field names in this document are surrounded by square brackets []. Query Design Screen
Query Design populated with some fields from two tables. The link between the two tables is illustrated with the line connecting the fields [Mem_Zip] in tbl-Members with [Zip] in tbl-Lookup_Zip. The query results will be sorted by the data in the [Mem_ID] field.

Filter Criteria
Queries provide the key to record filtering in database applications. Filters can be simple criteria like the one in the image below, fields from other tables or queries, or they can be very complex formulas. Query with criterian '76049' in the [Mem_Zip] field.
This screen shot below shows the [Mem_Zip] field with the criterian "76049" entered.

Here are some other examples of criteria used in the Members application:

Calculated Fields
Calculated fields are beyond the scope of this help file. For information on calculated fields see Help: Queries for the MG Members application.

A Word to the Wise
All of the queries of this application control the data included in one or more reports. If a query is changed in any way, the report will not work correctly if at all. Only the "Custom List" query is intended for users to enter their own criteria.

If you want to experiment with criteria or any other feature of a query, please work on a copy of the original. Here's how to make a copy of a query and use it for your experiments or new reports:
  1. If necessary click the >> just below the floppy disk icon to open the Navigation Pane.
  2. Select a query that you wish to study or experiment with.
  3. Right-click the query of interest and choose Copy (or press Ctrl-C).
  4. Right-click anywhere in the Access window and choose Paste (or press Ctrl-V) and type a new name such as MyTestQuery.
  5. Right-click your new copy with the new name, and choose Design View, to open the query.
  6. Select a field such as [Mem_Zip] and enter a zip code. Click the little floppy disk icon near the upper left corner of the work space to save the query.
  7. Click the datasheet icon at the upper left corner of the work space to run the query and see the results of your criteria.
  8. Click the design icon at the upper left corner to put your query in design view to continue your experiments.
  9. When finished click the X at the upper right corner of the query to close it. To delete your experimental query, select it in the Navigation Pane and press the delete key. Please use caution that you are selecting the correct query any time you make a change.
  10. When finished click the << at the upper right corner of the Navigation Pane (beside the All Tables bar) to close it.