Help: Queries

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 where necessary. 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.

The illustration below displays the first few fields of the qry-All-Names query in datasheet view. The lower case "qry-" section of the name identifies the object as a query.

All field names in this document are surrounded by square brackets []. The code that makes up formulas and criteria is expressed in Courier New font.

For help with query basics and how to build a query see Query Basics.

Custom List
The Main Menu invites you to open a Query for a custom list. What do you do with a custom list? The query opens in Design Mode, with the field, [Mem_Sel] from tbl-Members listed first. This makes it convenient for you to enter a short code of your own design on the Criteria row. You'll find this same field on the Members Entry/Edit form just above the Member Positions section of the form. It is labeled, "Select Code Up to 10 Characters: beside the text box to take your entry. Find the people you want included in your custom list, and enter the identical code in that field that you entered in the custom query. You can recycle this query again and again to create different custom lists.


No eMail List
This list was originally intended for use by the auxiliary to keep track of any members that had no e-mail, so they could be contacted by phone. With so many representatives being added to the database with unknown contact information, this list has grown from a handful to well over a hundred. This list could become useful again if filtered with a custom filter entered in both the query and the member records so that only a targeted few were included in the list. This query name is qry-MM-MailingList - No eMail.
Calculated Fields
This screen shot shows calculated fields illustrating that fields in a table can be manipulated in a query, and that data do not have to be stored in the table in the same form that it is retrieved. For example the [Full_Name] field is calculated from the [Mem_First_Name] and [Mem_Last_Name] fields; [Their_Name] is calculated from those fields plus [Mem_Spouse] which is stored in the table, but not displayed in the query. The [CSZ] (City, State, Zip) field is calculated from the tbl-Lookup_Zip table using the [Mem_Zip] field as reference to find the right city in that table. "ENTRYMYSAMPLE" was used as the criterian to pull this sample entry for this screen shot to protect the privacy of people whose names are actually stored in this database. Normally, this query uses no criteria to filter the data, e.g. All people who's contact information is stored in the Members table will be displayed in this query.


Below is a screen shot of the same section of the same query in in design mode.


The upper section of the query design window shows the tables that are used to construct the query. The related fields are identified by the lines connecting them. To give you an idea of how this works, the formulas, using a technique called concatination (meaning joining or adding together), for the three calculated fields are shown here:

Calculated Fields:

• [Full_Name]: [Mem_First_Name] & " " & [Mem_Last_Name]
• [Their_Name]: IIf(Not IsNull([Mem_Spouse]),[Mem_First_Name] & " & "
  & [Mem_Spouse] & " " & [Mem_Last_Name])
• [CSZ]: IIf(Not IsNull([Mem_Zip]),[City] & ", " & [ST] & " " & [Zip])

Filter Criteria
Queries provide the key to record filtering in database applications. The screen shot below shows three fields with criteria entered, [Auxiliary], [Aux_Dues_Year], and [Mem_Class.Value].


The query requires that the check box of the [Auxiliary] field in tbl-Mem-Positions be checked, indicating the member is part of the auxiliary. Then [Aux_Dues_Year] must be 2013. Those two eliminate all other records. On the right side of the image the [Mem_Class.Value] of tbl-Members must have a data pattern that is Like "Aux". Then the pattern, in this case "Aux" is surrounded with a pair of asterisks (*), the traditional wild card for PCs. Actually, the Yes under [Auxiliary] in tbl-Mem-Positions AND the Like "*Aux*" under [Mem_Class.Value] are not BOTH needed. Either one should do the job. It is easy to pull all the records for one zip code by simply entering it surrounded by quotes on the Criteria row of the zip code field of the appropriate table (tbl-Members or tbl-Corporations).

Here are some other examples of criteria used in this 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 "*Mission*" — Eliminates any record including the word Mission 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.

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.

---