Uncle Jim's Web Designs
Development Tools for the Internet and Your Computer

Microsoft Access Tutorial


Sorting and Filtering Queries Forms
Introduction to Queries

Queries select records from one or more tables in a database so they can be viewed, analyzed, and sorted on a common datasheet. The resulting collection of records, called a dynaset (short for dynamic subset), is saved as a database object and can therefore be easily used in the future. The query will be updated whenever the original tables are updated. Types of queries are select queries that extract data from tables based on specified values, find duplicate queries that display records with duplicate values for one or more of the specified fields, and find unmatched queries display records from one table that do not have corresponding values in a second table.

Create a Query in Design View

Follow these steps to create a new query in Design View:

  1. From the Queries page on the Database Window, click the New button.
    [New Query]
  2. Select Design View and click OK.
  3. Select tables and existing queries from the Tables and Queries tabs and click the Add button to add each one to the new query.
  4. Click Close when all of the tables and queries have been selected.
    [Show Table window]
  5. Add fields from the tables to the new query by double-clicking the field name in the table boxes or selecting the field from the Field: and Table: drop-down menus on the query form. Specify sort orders if necessary.
    [Select Query window]
  6. Enter the criteria for the query in the Criteria: field. The following table provides examples for some of the wildcard symbols and arithmetic operators that may be used. The Expression Builder [Expression Builder button] can also be used to assist in writing the expressions.

    Query Wildcards and Expression Operators
    Wildcard / OperatorExplanation
    ? StreetThe question mark is a wildcard that takes the place of a single letter.
    43th *The asterisk is the wildcard that represents a number of characters.
    <100Value less than 100
    >=1Value greater than or equal to 1
    <>"FL"Not equal to (all states besides Florida)
    Between 1 and 10Numbers between 1 and 10
    Is Null
    Is Not Null
    Finds records with no value
    or all records that have a value
    Like "a*"All words beginning with "a"
    >0 And <=10All numbers greater than 0 and less than 10
    "Bob" Or "Jane"Values are Bob or Jane

  7. After you have selected all of the fields and tables, click the Run button on the toolbar.
  8. Save the query by clicking the Save button.

Query Wizard

Access' Query Wizard will easily assist you to begin creating a select query.

  1. Click the Create query by using wizard icon in the database window to have Access step you through the process of creating a query.
    [Simple Query Wizard dialog box]
  2. From the first window, select fields that will be included in the query by first selecting the table from the drop-down Tables/Queries menu. Select the fields by clicking the > button to move the field from the Available Fields list to Selected Fields. Click the double arrow button >> to move all of the fields to Selected Fields. Select another table or query to choose from more fields and repeat the process of moving them to the Selected Fields box. Click Next > when all of the fields have been selected.
    [Simple Query Wizard dialog box]
  3. On the next window, enter the name for the query and click Finish.
  4. Refer to steps 5-8 of the previous tutorial to add more parameters to the query.

Find Duplicates Query

This query will filter out records in a single table that contain duplicate values in a field.

  1. Click the New button on the Queries database window, select Find Duplicates Query Wizard from the New Query window and click OK.
    [New Query window]
  2. Select the table or query that the find duplicates query will be applied to from the list provided and click Next >.
    Find Duplicates Query Wizard
  3. Select the fields that may contain duplicate values by highlighting the names in the Available fields list and clicking the > button to individually move the fields to the Duplicate-value fields list or >> to move all of the fields. Click Next > when all fields have been selected.
    Find Duplicates Query Wizard
  4. Select the fields that should appear in the new query along with the fields selected on the previous screen and click Next >.
    Find Duplicates Query Wizard
  5. Name the new query and click Finish.
    Find Duplicates Query Wizard

Delete a Query

To delete a table from the query, click the table's title bar and press the Delete key on the keyboard.

Sorting and Filtering Queries Forms