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.
- 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.
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'.
After you have placed 'Addr2' in a field column, edit the field name as follows:
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.
How do you perform a calculation with two or more fields?
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!)
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'
Field Names in Different Tables
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
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.
What you can't do
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.
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.
A common error in creating calculated fields is to try and use
the label name of one calculation in another field column name.
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:
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.
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.
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