MS-Access queries

1.  Introduction:

The Query design screen provides a mechanism for selecting specific data from data table(s), by:

• displaying only selected fields        (Can also change the order of fields)
• only records that match given criteria
• reorder records

2.  MS-Access Query Screen:

Tables Panel :       contains top section which shows the tables to be used in the query
Selection and Criteria Panel:       contains fields and criteria used in the query


Selecting Fields:

• Highlight the field in the desired table.
• Drag the field from the desired table (top of screen) and drop it in the field view skeleton (bottom section of screen).

3.  Selecting Records using a Criteria:

Enter criteria into criteria lines at the bottom section of the query design screen.

3.1 Criteria operators:

•       >
•       <
•       >=
•       <=
•       Between value1 AND value2
•       value1 OR value2
•       Not value1
•       Like 'string'

Different field types specify criteria slightly differently:

3.2 Like operator can contain Wild characters:

•      ? (match exactly one character)
•      * (match zero or more characters)
• eg:    Like ìb?llî     will match ìballî, ìbell, ìbillî..     
•         Like ìb*llî     will match all those above, plus words like ìbraillî, ìbeautifullîÖ

3.3 Character field:

• Data is entered into the query skeleton as is or encased in quotes.

3.4 Numeric fields:

• enter the number directly

3.5 Date fields:

• enclose the date within #  eg.  #24/03/2006#
• do not assume the format of the date.
  eg. is #03/04/06#    3-Apr-2006   or    4-Mar-2006    or    6-Apr-2003  (yy-mm-dd)?
  (Actually - it depends on your computer's system date setting!)
• Solution: enter dates unambigously:   #03-Apr-2006#

3.6 Logical Fields:

• Enter  Yes, True, No or False

eg. 1:

eg. 4:

4. Multiple Criteria:

4.1  Condition1  AND  Condition2

• Enter conditions on the same line. Department="Accounting"  AND  Pay_Rate > 7.00

eg. 2.

Pay_Rate between 7.00  AND  9.50

          OR

4.2  Condition1  OR  Condition2

Enter conditions on different lines in table skeleton:

eg. 1: Department is either "Accounting" OR  "Computing"

eg. 2: Pay_rate > 9.00 OR Department = "Computing"

5.  Ordering of records:

MS-ACCESS sorts ascending/descending on most of the data types 
ie. text, numeric, date and yes/no fields.

6. Concatenating Fields:

• eg. joining the firstname and lastname of a person into a single fullname
• useful for comboBoxes
• use concatenation operator '&' or '+'
• syntax for the column's field expression:
  newColName: concatenationExpression

7.  Linking Tables:

Performed within a query. Usually linked on a common field.
eg. Table1: Sales table (FLYSALE)

Table2: Inventory table  (FLYINVEN)

Results of linked query:

copyright © 2006 Brian Retallick. This page last updated on Thursday 28 August 2008 by Noel McEwan, La Trobe University, Bendigo