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:
- From the Queries page on the Database Window, click the New
- Select Design View and click OK.
- Select tables and existing queries from the Tables and
Queries tabs and click the Add button to add each
one to the new query.
- Click Close when all of the tables and queries have been
- 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.
- 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
can also be used to assist in writing the expressions.
|Query Wildcards and Expression
|Wildcard / Operator
||The question mark is a wildcard that takes the place of
a single letter.
||The asterisk is the wildcard that represents a number
||Value less than 100
||Value greater than or equal to 1
||Not equal to (all states besides Florida)
|Between 1 and 10
||Numbers between 1 and 10
Is Not Null
|Finds records with no value
or all records that have a value
||All words beginning with "a"
|>0 And <=10
||All numbers greater than 0 and less than 10
|"Bob" Or "Jane"
||Values are Bob or Jane
- After you have selected all of the fields and tables, click
the Run button on the toolbar.
- Save the query by clicking the Save button.
Access' Query Wizard will easily assist you to begin creating a
- Click the Create query by using wizard icon in the database
window to have Access step you through the process of creating
- 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.
- On the next window, enter the name for the query and click Finish.
- 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.
- Click the New button on the Queries database window,
select Find Duplicates Query Wizard from the New Query
window and click OK.
- Select the table or query that the find duplicates query will
be applied to from the list provided and click Next >.
- 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.
- Select the fields that should appear in the new query along
with the fields selected on the previous screen and click Next
- Name the new query and click Finish.
Delete a Query
To delete a table from the query, click the table's title bar and
press the Delete key on the keyboard.