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!

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, click > button. Repeat.

Some fields have been selected.

6. Select Detail radio button.

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 [].

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.

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

• Is Not Null — Makes sure there is actually some data in the field of interest.
• Is Not Null Or Is Null — Sometimes you don't have complete data for all records, e.g. the street address is missing. Use this criterion in such a field to allow records to be returned in the query regardless of whether or not this field has data.
• Is Not Like "*Texas*" — Eliminates any record including the word Texas in this field.
• Val(Left([Mem_BDay],2)) And Month(Date())+1 — Under [mon-Num] selects next month (this month + 1). Birthdays are 4-digit text entries—that is, they are not numbers in the arithmatic sense. Single-digit month numbers are preceded by zeros. This formula selects the first two characters representing the month and changes them to a number. This must match the number of the current month returned by the built-in Date() function plus 1. All this tells the query that we want to see the records where the birth month is next month.
• Like "*[tbl-Lookup_Class.Class]*" — Fields holding more than one value require some special attention when the list should be sorted on values in that field. This isolates each of the values in Mem_Class so they can be sorted in the resulting data. This scheme will pull records of individuals for each class of participation.

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.

---