The first step in creating a query is to select the necessary table(s) and/or queries that contain the data necessary for the desired results. Each source selected should have a connection (link or join) value.

Once the data sources have been selected, field(s) between tables/queries have to be linked (joined) to enable the correct selection of data. You can simply click and drag from a field in one table to the linked field in another table. After the link has been established, a decision has to be made as to type of join.

Join options:

• Only include records where there is match in both tables
• Include ALL records from one table and only include records in the other table where there is a match

To change the join method, double click on the linkage line and a window will pop-up with the options to change the join type.

top of page

The next step is to select the fields that you want to appear in your query. Simply click and drag a field from a table to the field column.

Relabel

Sometimes it is convenient to use a different name for the field. The table has a field labeled 'Addr2', but within the query you want to refer to this as 'Address'.

Example:
After you have placed 'Addr2' in a field column, edit the field name as follows:

• Address: [Addr2]

This syntax says get information from the field 'Addr2' and label the column as 'Address' when the output is produced. Basically anything before the colon will appear as the title for the column.

Calculated Values

How do you perform a calculation with two or more fields?

Example:
YYou have the fields 'Price' and 'Qty' in your table and only want to produce the 'Extension' results of price X quantity. Edit the field name as follows:

• Extension: Extension: [Price] * [Qty]

What if you want to calculate different results based on some rule or value? In this case you need to incorporate the iif function. (Yes, that are two i's in if!)

The iif function format is...

• If this, do this, else do this

Example: Let's assume you want to calculate taxable amounts and your table has the fields 'Price', 'Qty' and'Taxable' as a Yes/No field. Edit the field name as follows:

• Taxable: iif([Taxable]=-1

This states if the line is taxable (-1=true), calculate the taxable amount, otherwise use a value of zero for this record.

What if you want to display the last name of an individual and only the first letter of the first name?

• Employee: [LastName] & ", " & left([FirstName],1)

• left function has two parameters
  • Field name
  • Number of characters to extract starting with the first character.
• right function is similiar only starts from the rightmost character.
  • right("ABCDEF",2) would extract and display 'EF'
• mid function has three parameters
  • Field name
  • Starting position
  • Number of characters to extract
  • mid("ABCDEF",3,2) would extract and display 'CD'

Count Records

Sometimes it is necessary to get a count of how many records are included in the query. You could create a numeric field in the table that always has a value of 1, but this is a waste of good table space. It can be accomplished in the query. In a new field name column, enter a value of

• Count: 1

This will output a column labeled count with a value of 1 for each record. By either using the Totals option with grouping or the sum option in a report, you can get the value of the total number of records.

Duplicate Field Names in Different Tables

You will run into the situation where you have the same field name in two different tables and want to create an expression using this field name. If you noticed, that whenever you used an expression in the field name, the table name disappeared. Because of this, you have to refer to the table + field name in order to tell the query from which table to pull the information.

Example:
You have a query that uses the INVOICEDETAIL and INVENTORY tables where both tables have the field name Price.  Again, using our above example for the extension calculation this would become:

• Extension: [INVOICEDETAIL].[Price] * [Qty]

Now you have specified from what table to pull the Price data.

What you can't do

A common error in creating calculated fields is to try and use the label name of one calculation in another field column name.

Example:
Using the above Extension example, you can't use 'Extension' in another calculated field. Let's assume you want to calculate 'Profit' and your table already has a 'TotalCost' field. It may seem reasonable to use this expression:
Profit: [Extension]-[TotalCost]
Sorry friends, won't work. The correct expression is

• Profit: ([Price] * [Qty]) - [TotalCost]

Note: The use of ( ) to get the correct calculation.

top of page

This is an easy option. If you want to sort on a field, select Ascending or Descending. If you select more than one field for sorting, make sure the fields are organized left to right in order to get the correct sorted results. 

Example:
You have a table that has CustID, InvoiceNum, DueDate and you want the data listed by customer, with the oldest invoices listed first.

• First column should be CustID, sorted ascending
• Second column should be DueDate, sorted descending

If you want to list the data by oldest invoice and not grouped by customer, then the first column should be DueDate.

If the query is going to be used for a report, don't add any sort options in the query. The sorting and grouping options are controlled within the report.

top of page

There are situations where you may want to only select certain records based on some criteria for a given field. You don't need or want to display the field used for the selection criteria. Simply uncheck the Show box. The same can be true for sorted fields.

top of page

The criteria row starts to add power and flexibility to your query. Here are a few examples for criteria usage.

• To only select customers in the state of Illinois. In the 'State' field enter IL
• To select records that contain either IL or WI IL or WI
• To select records with in a given date range >=1/1/00 <=1/31/00This would only include dates for Jan 2000.
• To select records with today's date Date()
• To select records for a given day of any month Day([DateField])=5This would select all records on the 5th of any month
• To select records for a given month. Month([DateField])=2 This would select all records for February of any year
• To select records for a given year Year([DateField])=1998 This would select all records for 1999
• To select records for a given weekday Weekday([DateField])=2 This would select all records for any Monday
• To select records for a given quarter DatePart("q",[DateField])=2This would select all records for any 2nd quarter
• To select all names that start with 'General'. Like "General*"
• To select all names that contain 'Inc'. Like "*inc*" Note: case is ignored
• To select Yes values for a 'Yes/No' field -1
• To select No values for a 'Yes/No' field 0
• To select records where a numeric field is in the range of 10-99 or is less than >=10 and <=99
• To select records where a field contains a 'null' value Is Null
• You have the form 'fCustomer' open with a specific customer record selected. To select records that match the current CustID field in the form. Forms![fCustomer]![CustID]

top of page

To turn on the totals option, click on View, Totals. This options allows you to group records based on matching values.

Example:
You want to only get totals for each customer from the INVOICEDETAIL table. This table contains several records (invoices) for each customer. By turning on the totals option, existing fields automatically are set to Group By.

This is what you want except for the 'Extension' column. Click on the totals row for 'Extension' and select Sum.

Note: There are several other totals option: min, max, first, last, average, etc.

top of page