LIS 558 - Query by Example

Microsoft Access allows you to use QBE (Query by Example). The prevailing standard in client server application is SQL (Structured Query Language). You can see the SQL version of your query by selecting "View|SQL View" from the menus in Design mode.

  1. The Simple Query Wizard takes you through the following steps:
    1. Specify which fields from which tables or other queries to use.
    2. Select whether you want a detail or summary query and specify details for a summary query.
    3. Name the query and choose to run it or modify the design.
  2. In Design View, the rows in the QBE table are as follows:
    Field Allows you to select a field from one of the tables or queries used to construct the query.
    Table Allows you to select one of the tables or queries used to construct the query, so that you can select a field.
    Total
    (summary queries only)
    Allows you to specify various summary and grouping operations: GroupBy, Sum, Average, Min, Max, Count, StDev, Var, First, Last, Expression, Where
    Group By is used to define groups for summary queries; Expression is used for entering calculated fields; and Where is used for criteria from fields that will not be shown.
    Sort Allows you to specify Ascending or Descending or not sorted by that field.
    Show Allows you to specify whether the field is included in the query result. (You may need to "show" a field in a query even if you do not want to show it in a form or report based on the query.)
    Criteria Allows you to enter conditions which the field must satisfy in order for the record to be included in the result.
  3. General points about criteria:
    1. Exact matches are expressed by a literal word, phrase, or number. Words or phrases may be enclosed in quotation marks ("").
    2. To match an empty field, use Is Null.
    3. Use Between...And... for ranges.
    4. Use Date() for today's date.
    5. Wildcard symbols include * (any character sequence), ? (single character), and # (single digit); you can also specify ranges of characters in [] (see "Like Operator" in the Microsoft Access Help for more information).
    6. Boolean operators include And, Or, and Not.
    7. And is implied between fields and Or is implied between criteria rows within a field.

Home

Last updated July 5, 2001.
This page maintained by Prof. Tim Craven
E-mail (text/plain only): craven@uwo.ca
Faculty of Information and Media Studies
University of Western Ontario,
London, Ontario
Canada, N6A 5B7