Ch 4 -- Queries and Filters
Microsoft® Access 97 Quick Reference
- 4 -
Queries and Filters
Queries and filters are primary ways to find and organize your data. A filter
is saved with a table and can't be reused for other objects unless you save
it as a query. Queries can be used to answer questions themselves, but can
also be used as the foundation for forms and reports.
Creating queries and filters involves tasks such as sorting data, choosing data
(setting criteria), and selecting tables and fields. Queries
are more versatile than filters and you can use them to mass update, delete, and
add records to a table. You can also do substantial calculations with queries.
NOTE: For more on the calculation capabilities
of queries see the Calculations part of this book.
Most of the following procedures start in Query Design View. You can enter
this view by selecting an existing query on the Database window and choosing
the Design button. You can also get to the design view by selecting New on
the Queries tab of the Database window and choosing Design View.
Action Query: Append Query
An append query provides a method for adding records to one or more tables. This
is useful when you want to transfer records from one table, or set of related tables,
to another. You can also use the append query to write data to the same fields in
matching records between two tables. For notes about action queries, see "Action
Query: Create" first.
Steps
- 1. In the Database window, highlight the table or query for the
basis of the append query and choose Query from the New Object button on the toolbar.
The New Query dialog box is displayed. Click OK to accept the default.
- 2. Click the Query Type button on the toolbar and select the Ap pend
Query option. Enter the name of the target table in the Table Name box. Click
either Current Database or Another Database to specify the target table;
then choose OK.
- 3. Drag the fields from the Field List that you want to append
to the Query Design grid, along with any fields you will use for selection
criteria.
- 4. Enter the criteria for creating the result set in the Query Design
grid.
- 5. Click the View button on the toolbar to preview the records to append.
- 6. Click the Run button to append the records to the table(s) you specified.
NOTE: If you want the target table to
automatically add new AutoNumber values, do not drag the AutoNumber
field onto the Query Design grid. To copy the AutoNumber values from the source table,
drag the AutoNumber field onto the Query Design grid. If the target table already
has AutoNumbers that match the source table, the Append Query will not append those
records when you Append to the AutoNumber field.
Also, when all fields have the same name in both tables, drag only the asterisk for
the table to the Query Design grid.
Action Query: Create
An action query collects records that meet your search criteria,
and changes the data contained in those records in a single step. Access allows four
different action queries: delete, update, append, and make-table query. A delete
query removes the groups of records returned from your query from your table. An
update query alters the information contained in your result set and writes the changes
back to your table. An append query adds the records from your result set
to your table. Finally, a make-table query creates an entirely new table from your
result set.
CAUTION: Because action queries modify
your data, it is a good idea to first create a select query with the fields
and criteria you want. View the results of the query to make sure you have
the correct records. Then, turn the select query into an action query by selecting
one of the options on the Query Type button on the toolbar.
First follow the steps to create a select query. (See "Query: Create with
Design View" or "Query: Create with Wizard.")
Steps
- 1. In your query's Design View, enter criteria, add a sort order,
and create any calculated fields. Click the Datasheet View button to make
sure your criteria are OK.
- 2. Return to the design view by clicking the Design View button.
- 3. Turn the query into an action query by clicking the drop-down arrow
on the Query Type button and choosing one of the four action query types (Make-Table,
Update, Append, Delete).
- 4. If prompted, identify the target table. Click the Run button to perform
the action query.
- 5. Click the Save button on the toolbar. Enter a name in the Save dialog
box, then choose OK.
The action query will appear on the Queries tab of the Database window.
To indicate that it will run when opened, the icon next to the name includes an exclamation
point.
CAUTION: Be careful when you double-click
or choose the Open button for an action query. The query runs every time. You might
want to delete action queries when you no longer need them to avoid accidentally
updating your data. You can also hide the query by choosing the hidden object
property. (See "Database Object: Properties" in the File Management
part of this book.)
Action Query: Delete Query
The delete query enables you to remove a set of records from the table
you specify. If you have already created a relationship that enforces referential
integrity with cascade deletes, you will only need to delete records from
parent table. (See "Relationships between Tables" in the Table and Database
Design part of this book.) The records from the related child table will delete
automatically.
Steps
- 1. In the Database window, highlight the table or query for the
basis of the delete query and choose Query from the New Object button
on the toolbar. Click OK.
- 2. Drag any fields you need to identify records into the design grid.
Then click the Query Type button on the toolbar and select the Delete Query
option.
- 3. Enter any criteria for deleting records, and they appear under Where
in the Delete cell.
- 4. Click the View button on the toolbar to preview the records to be deleted.
- 5. Click the Run button to delete the records from the table.
After closing the dialog box, the records in your result set are removed
from the table you specified.
Action Query: Make-Table
The make-table query enables you to create a table for export; it provides the
basis for a report; it provides a method for making backups; and it gives
a snapshot of your data at a point in time. The make-table query also enables you
to improve the performance of your forms and reports by working from a set of records
stored to disk that don't have to be retrieved from a large data set or from a network.
You can also copy a table from the Database window and if necessary, delete
or add any additional fields you want. (See "Database Object: Copy" in
the File Management part of this book.)
Steps
- 1. In the Database window, highlight the table or query
for the basis of the make-table query and choose Q_uery from the New Object button
on the toolbar. Click OK.
- 2. Drag any fields you need to identify records into the design
grid. You can also add an additional table and any other fields from the second
related table if you want to use fields from more than one table. Then click the
Query Type button on the toolbar and select the Make-Table Query option.
- 3. Enter a name in the Table Name text box, select either
the Current Database or Another Database option button, then
choose OK.
- 4. Enter any criteria for choosing records in the Criteria
row. Click the View button on the toolbar to preview the records to be included.
- 5. Then Click the Run button to create the new table.
After dismissing the dialog box indicating that a new table will be created,
Access writes your result set to disk as a table in the database you specified.
Action Query: Update Query
An update query can make global changes to selected records in a table
or a set of related tables. This type of query is useful for replacing information
quickly.
A common example for an update query would be where you would increase
the price for an item. In the Update To cell under the price field in the
Query Design grid you would type the expression [Price]*1.02
(to increase the price by 2%). For more information on calculations, see "Calculated
Fields: Queries Create" in the Calculations part of this book.
Steps
- 1. In the Database window, highlight the table or query
for the basis of the update query and choose Query from the New Object
button on the toolbar. Click OK.
- 2. In the Query Design View, click the down arrow next to the Query
Type button on the toolbar, then select the Update Query option.
- 3. Drag the fields from the Field List to the Query Design grid that will
be in the new table. Enter the criteria for your result set, and any sort you want.
- 4. In the Update To cell, enter the expression or value to be used as
a replacement for the field(s) selected.
- 5. Click the View button to see a list of records that will be updated.
Then click the Run button on the toolbar.
After closing the dialog box, Access replaces your old data with the new
values in the result set you specified.
Criteria: Blanks, Nulls, and Empty
When you create select or action queries or use the Advanced Filter/Sort option
to create a filter for a table, you often might want to see which records
have no values. For example, you might want to find all addresses without zip codes
so you can look them up before you do a mailing. You may also want to find which
fields have zero-length strings.
When you use a null field in a calculation, the result of a calculation
is null. You can use the NZ function to convert nulls to zeros. (See "Blanks,
Empty, and Null: Work With" in the Calculations part of this book.)
NOTE: For a field to be capable of accepting
zero-length strings, that field property must be set to Yes. (See "Data:
Blanks, Nulls, and Zero-Length Strings" in the Table and Database Design part
of this book.)
Steps
- 1. In Design View of a query, click in the criteria
row under the desired field.
- 2. To find all records with no entry, enter Null. Access
will translate this to Is Null. If you want to find all records except blanks,
type Not Null (which Access enters as Is Not Null).
- 3. To find records with zero-length strings, enter ""
(two quotes with no space).
- 4. In the Field cell of the design grid, if you are calculating
values that have nulls in some of the fields, enclose each field name in the NZ([Fieldname])
function.
- 5. Choose the Datasheet View button to see the results of the query
or choose the Run button to update records if the query is an action query.
TIP: To display zeros for a numeric field
that has nulls, type the following in the Field cell of the Query Design grid: NZ([Fieldname])+0.
NOTE: When you sort ascending on a field
with Nulls, the records with the Null fields display first.
Criteria: Date
When you are working with date fields, there are some criteria you
might want to keep in mind. You can choose to show just today's date or a specific
month, day, or year. Another option is to include a range of dates.
Steps
- 1. In Design View of a query or filter, click in
the criteria row under the desired field.
- 2. If you want to enter a specific date, type the date. Regardless of
the format for the date, you can enter the date in its simplest format for the criteria:
For example, 1/1/98. When you leave the criteria cell Access translates this
to #1/1/98#.
- 3. If you want to find all records with today's date, type Date().
If you want all those after today's date, type >Date(). If you want all
those up to and including today's date, type <=Date().
- 4. If you want all records between two days use the Between And expression
as in the example: Between 1/1/97 and 3/5/98.
- 5. To find all records for a specific month, year or day, use the *
(asterisk). 1/*/98 finds all records in January 1998. */*/92 finds
all records in 1992.
- 6. Choose the Datasheet View button to see the results of the query
or choose the Run button to update records if the query is an action query.
For date functions, see also "Functions: Date" in the Calculations
part of this book.
Criteria: Multiple
If you are searching for data that meets more than one criteria, you can use the
Query Design grid or the keywords AND and OR.
To enter criteria, be sure you are in the Design View of a query or filter.
Steps
- 1. To set criteria for multiple fields where all criteria must be met
(for example, City is Denver and Address includes Broadway), type the
expression for the first field, move to the second field in the same
criteria row and type the second expression. Repeat for all necessary fields.
- 2. To set criteria for multiple fields where any criteria can be met,
type the expression for the first field, move down to the next criteria row (labeled
Or) and type the second expression under the second field. Move down again
if there is a third criteria.
- 3. If you are looking for multiple possibilities in the same field, type
in one criteria cell and type OR between each expression (for example: CO
or NE). If you have many entries you can use the IN keyword (for example,
IN(CO, AL, NE, KS).
- 4. If one field must meet two criteria use the AND keyword (for
example >5 AND <10).
- 5. Choose the Datasheet View button to see the results of the query or
choose the Run button to update records if the query is an action query.
Criteria: Numeric
When you are looking for numeric data, there are some handy expressions for use
with numeric criteria. To enter criteria, be sure you are in the criteria
cell of the Design View of a query or a filter.
Steps
- 1. Type the number in the criteria cell to see just this value.
- 2. Type <Number or <=Number to see all numbers less
than or less than or equal to the number.
- 3. Type >Number or >=Number to see all numbers greater
than or greater than or equal to the number.
- 4. Type Between Number1 and Number2 to find all numbers
between two numbers (including both numbers).
- 5. Type >Number1 and <Number2 to find all numbers
between two numbers (not including both numbers).
- 6. Choose the Datasheet View button to see the results of the query
or choose the Run button to update records if the query is an action query.
Criteria: Specify
You can enter selection criteria into filters and into queries.
Queries are more generally useful in that they can work with related tables of records
and direct the output of the result set to several different places. Filters apply
only to the current table or query and cannot be used elsewhere.
Steps
- 1. Move to the criteria row and enter the expression into the design
grid of the Query or Filter.
- 2. If you cannot see the entire criteria, press Shift+F2 to expand the
cell into a window. Choose OK when finished editing the criteria.
- 3. If you want to enter complicated criteria, click the Build button on
the toolbar and use the Expression builder.
Access allows for a wide range of values and expressions, and the Query Design
View is a powerful tool for composing queries and applying criteria for selection.
For some of the criteria types, see the other Criteria sections in this part.
(See also "Expression: Create with Builder" in the Calculations part of
this book.) Access translates the query into SQL and applies it to the appropriate
data sources (tables and grids) that the query operates on.
Criteria: Text
Most of your data will be text data type. There are some handy expressions
for use with text criteria. To enter criteria you need to be in the criteria cell
of the Design View of a query or a filter.
Steps
- 1. Enter in the complete text match you want. Access puts quotes around
the expression.
- 2. Include the asterisk (*) wild-card character for any text you
are not sure of. A single asterisk can take the place of any number of characters.
You can also use multiple asterisks in one expression. Jo*ns*n will find Johnson,
Johanson, Jonsen, and Johnsen. Access enters Like "Jo*ns*n" in the
criteria cell.
- 3. Enter Not and then the text you don't want to match. Not
NY will find all states except NY.
- 4. Use the question mark (?) as a wildcard character for one letter.
For example, C? would find CO, CT, and CA.
- 5. Choose the Datasheet View button to see the results of the query
or choose the Run button to update records if the query is an action query.
Criteria: Use Another Field
In some instances you want to use one field in a criteria for finding
records based on another field. For example, you might want to find all records
where the Promised Date is less than the Shipped Date and send the clients an apology.
Steps
- 1. In Design View of a query or filter, move to the
criteria row of the field.
- 2. Type any operators and then the second field's name in square brackets.
For example, <[ShippedDate].
- 3. Choose the Datasheet View button to see the results of the query or
choose the Run button to update records if the query is an action query.
Duplicates: Find
One of the most frustrating parts about managing data is trying to dispose of
duplicates. Access can show which records have exact duplicates. You can then decide
which record to delete. Before you create a primary key on a table
with existing data, you also need to search for and remove duplicates.
Steps
- 1. On the Queries tab of the Database window, choose New
and double-click Find Duplicates Query Wizard.
- 2. On the first step of the wizard, double-click the table or query.
- 3. Double-click the field (or combination of fields) that will
identify duplicates; choose Next.
- 4. Double-click any additional fields you want to display in the query;
choose Next.
- 5. Give your query a name and click the Finish button.
The query will list only duplicated records in the table. You can modify the fields
or delete the unnecessary records. (See "Records: Delete" in the Database
Essentials part of this book.)
TIP: Access cannot show you which records
have close matches. For example, if you have 491 Fox St and 491 Fox
Street, Access will not show these as duplicates. You might want to develop
standard rules for entering data (always spell out or use abbreviations, for example).
Duplicates: Remove
You can delete duplicates one at a time from the result of a Find Duplicates Query
Wizard. (See "Duplicates: Find".) However, if you have many duplicates,
you might want to use the following procedure. This procedure does not allow you
to choose which record of the two duplicates you want to delete.
Steps
- 1. In the Database window select the table and click the
Copy and Paste buttons on the toolbar. When prompted copy the structure only and
give the new table a name.
- 2. Open the new table in Design View, hold down Ctrl, click the field(s)
with duplicated information, and click the Primary Key button on the toolbar.
Save and close the new table.
- 3. Select the first table and choose Query from the New Object
button. Include all fields from the table in the query grid by double-clicking
the asterisk in the Field List.
- 4. Choose Append Query from the Query Type button and enter your
new table name.
- 5. Click the Run button to run the query and answer Yes to the prompts.
This query works because the primary key in a table cannot be duplicated. After
you view your second table to make sure the information is correct, you can delete
the first table and rename the second. (See "Database Object: Delete" and
"Database Object: Rename" in the File Management part of this book.)
Filter Data: Advanced Filter
The most complex filters you can create enable you to both filter and sort
records in a single operation for a single table or query. The
Advanced Filter/Sort window is similar to the Query Design View in construction
in that you work in a design grid where you specify the criteria used to filter
your records and the sort order. The Advanced Filter/Sort feature can operate on
tables or queries, but cannot provide related tables.
Steps
- 1. Open a table, query, or form in the Datasheet View, or
a form in the Form View.
- 2. Choose Record, Filter, Advanced Filter/Sort.
- 3. Enter the criteria and sort you want in the design grid.
- 4. Click Apply Filter on the toolbar.
Filter Data: By Form
If you want to select a set of records in a datasheet or a form, the simplest
way to do this is to set a filter. A filter is a single set of criteria
that can be applied to your data set. When you apply another filter to a result set
from a previous filter, you narrow your result set even further.
Access remembers your last filter in a session and lets you reapply it at any
time. If you apply a filter to a table or form, Access remembers that
filter until you apply a new one. Filters applied to queries are not entered
into the query grid, but can be applied later separately. You can also sort
filtered records. You might find that filters provide much of the find and
query capability you need in your work.
Steps
- 1. Open a table, query, or form in the Datasheet or Form View.
- 2. Click the Filter By Form button on the toolbar to open a Filter By
Form window.
- 3. Click the field you want to filter by and enter the selection
criteria that records must match to be returned in a result set.
- 4. To enter a value to search for, select that value from the list in
that field; or enter the value manually.
- 5. To enter a value of a check box, option button, or toggle
button, click that button. Entering a value in two fields on the same line in
the Look For tab (see the bottom of the window) requires both values to be matched
in the result set.
- 6. To perform a filter based on alternative values, click the OR tab at
the bottom of the window and enter additional criteria. Click the Apply Filter button
on the toolbar to perform the selection.
NOTE: Check boxes display three states:
checked (on), not checked (off), and mixed state (grayed). Make sure you place a
check mark into the condition you want. Also, you can select a field based on the
conditions Is Null or Is Not Null.
You can reapply the filter by clicking the Apply Filter button at a later time.
You can use the Remove Filter button to remove your current filter.
Filter Data: By Selection
You can filter the records shown in a form, subform, or datasheet
by applying a Filter By Selection. When you click the toolbar button, all records
that match your selected field display. This procedure is very simple to use,
but more limited than Filter By Form. (See "Filter Data: By Form.")
Steps
- 1. Open a table, query, or form in the Datasheet View,
or a form in the Form View.
- 2. Select the record that has the value you want to limit in a
form or datasheet.
- 3. Click the insertion point in the field (this will cause the
entire field's contents to be the criteria for the filter).
- 4. Click the Filter by Selection button on the toolbar.
NOTE: As mentioned in the preceding Step 3 you
can click in a field (with no text selected) to have Access use the entire field
for the filter. You can also select part of a field starting with the first character
to return records where the value in that field also starts with those characters.
Another option is to select any value after the first character in a field to return
all or part of a value in that field with the same characters.
Filter Data: Filter With Shortcut Menu
Access lets you enter a filter directly into a field on a field's shortcut
menu as a filter request.
Steps
- 1. Open a table, query, or form in the Datasheet
View, or a form in the Form View.
- 2. Right-click a field and enter a value or expression in the Filter
For text box; then press Enter.
By successively filtering, you can narrow your result set. You can press Tab while
still in the Filter For text box in Step 2 to apply the filter and add a new filter
criteria as well.
Filter Data: Save as Query
After working with a filter you might want to use it again under different
circumstances. You can save the filter as a query.
Steps
- 1. Create a filter from any of the methods mentioned in the filter tasks
in this part of the book.
- 2. Go to the filter design window by choosing Record,
Filter, Advanced Filter/Sort.
- 3. If desired, modify the filter grid and click the Save As Query
button on the toolbar.
- 4. Enter the name of the query in the Save As Query dialog box.
If you want to use the specification from this query in a filter again, go to
the filter design grid by choosing Record, Filter, Advanced
Filter/Sort, click the Load from Query button and double-click the query name.
This query now appears on the Query tab of the Database window. You can
use it like any other query. One difference is that all fields are displayed rather
than just the fields that are in the grid. This is because the Output All Fields
property is set to Yes.
List of Values
Sometimes you want to just see a list of all the different values in one field
with none of the items duplicated. You might want to create a lookup table
with these values, in which case you could create this query and then turn it into
a Make-Table Query (see "Action Queries: Make-Table Query") and use the
table in a lookup column (see "Lookup Column: Create with Wizard"
in the Table and Database Design part of this book).
Steps
- 1. In the Database window, select the table or query from which
you want to find the values. Choose Query from the New Object button on the
toolbar.
- 2. Right-click the mouse button on the query's title bar and choose Properties
on the shortcut menu. The Query Properties sheet opens.
- 3. Change the Unique Values property to Yes.
- 4. Double-click in the Field List the one field for which you want to
see values.
- 5. Close the Property sheet, save, and run the query.
Query Design: Expand Column
At any point during query design you might not have enough room to see
your field or criteria. You can change the column width temporarily
to see what you need at the moment, or you can permanently widen the column.
Steps
- 1. If you permanently want to see a wider column width, move to the gray
border above and between two field names. The mouse pointer will be a double-headed
arrow. Drag the mouse to the right to increase the column width.
2. If you want to temporarily see the entire entry, press Shift+F2 to open
the Zoom window. Choose OK when you are done viewing or editing.
Query Parameters: Prompt for Input
When you want one query to work as many, consider using a parameter query. For
example, suppose you need all the same information but want to change the salesperson
each time you run the query. When you create a parameter query, Access will
prompt you each time you run the query. This is true even when you open a form
or report based on this query.
Steps
- 1. Create the query with all the fields and other criteria you want and
open the query in Design View.
2. Move to the criteria grid under the field you want to check.
Type an open square bracket, any text you want for the prompt, and a close square
bracket. For example, type [Enter the salesperson's name:].
3. You might have more than one prompt and need to change the order of the
prompts or define a data type for the prompt (so Access displays a warning
if the wrong data is entered in the prompt). Choose Query, Parameters.
Enter the exact text of your prompt and choose a data type for each prompt.
4. Click the Run button. Access will show a dialog box with whatever
prompt you added. Enter your text.
5. Save and close your query.
Whenever you run the query (including just opening it) Access will display a dialog
box with the prompt you created.
TIP: Enter the keyword Like before
your prompt if you want to use wildcards.
Query: Add Field
When you edit or build a query you might need to insert fields in the appropriate
place on the grid. Open up any query in Design View to add a field.
Steps
- 1. To add a field at the end of the query grid, double-click the field.
- 2. To add a field between two existing fields, drag the field name from
the list box onto the right field.
- 3. To add all fields in the grid, first clear the grid if necessary with
Edit, Clear Grid.
Double-click the asterisk (*) in the Field List.
- 4. If you want to add all fields to the grid and you are going to use
sort or criteria from any of the fields, double-click the title bar of the list box
and drag from one of the fields to the grid.
Query: Add Table
You can use more than one table in the query. In almost all cases
the tables need to be related. Open up any query in Design View to add a table.
Steps
- 1. In Query Design View, click the Show Table button on the toolbar.
- 2. Double-click each table or query you want to add; choose Close
when done.
- 3. If necessary, draw a join line between the two related fields
in the Field Lists. Generally, you will be drawing a line between two fields
with the same or similar names (for example, EmpID and Employee ID).
- 4. Double-click fields from any of the Field Lists to add them to the
design grid.
For more details on the joins, see "Tables: Combine with Join" and "Tables:
Create a Self Join."
NOTE: If you accidentally add a table
twice to the design grid, click in the table and press the Delete key to remove
it. Don't forget that you may not see all the tables. Use the scroll bars in the
upper part of the grid to see if you have any more tables hiding in the grid.
Query: Change Column Heading Names
When you make calculated columns in a query, you will probably want to add a column
name instead of the Expr headings that Access defaults to.
Steps
- 1. In Query Design View, click before text in any Field
cell.
- 2. Type the new column name and a colon (:).
Query: Create with Design View
Queries are recipes for finding, selecting, and sorting data in tables. Access
stores the definition of a query, runs the query, and returns a result set
when the query is requested. Queries can be used as the underlying data set
for forms and reports. Queries can also be used to select, append,
delete, and update records in the database (see the appropriate "Action
Query" sections).
You create a new query using either the Query Wizard or the Query Design window.
Both are relatively easy to use, though the Query Wizard will walk you through the
process step-by-step. The Query Design Wizard provides you with additional options.
(See "Query: Create with Wizard.")
NOTE: You can select records by applying
a filter and sort those records. Access 97 remembers the last filter you applied
without you having to re-enter it (see the Filter tasks earlier in this part). This
is an alternative to running some queries.
Steps
- 1. Click the Query tab in the Database window, then choose the
New button. Select Design View in the New Query dialog box; then double-click
the table(s) you want from the Show Table dialog box, choose Close.
- If you add related tables, Access will automatically create join lines
between the tables.
To create a relationship between two tables, drag the key field of
the parent tables to the related field of the child table.
- 2. Double-click any fields from the Field Lists for each table you want
as part of the query.
- 3. Click in the Sort row and select Ascending or Descending for any field
that you want to sort.
- Fields are sorted in their order from right to left, the left field being the
primary sort key.
To re-order the fields, click and drag the column header.
- 4. Uncheck the Show check box for any field you want to hide in
the Query.
- Fields can be used in queries that do not appear in the query output.
- 5. Enter any expression in the criteria cell for that field. Click
Save in the Query Design toolbar. Enter the name of the form in the Query
Name text box of the Save As dialog box, then choose OK.
See the Criteria sections of this part for examples of criteria. When you
run a query, the data you see reflects the data that conforms to your query at runtime.
You can update, delete, or append the data in a result set for most queries. (See
"Updatable Queries.")
Query: Create with Wizard
The Query Wizard is easier for novices to create queries but provides less flexibility
than Query Design View. (See "Query: Create with Design View.")
Steps
- 1. On the Query tab of the Database window, choose the New
button and choose Simple Query Wizard.
- 2. Choose a table or query from the Table/Query drop-down
box.
- 3. Double-click any fields you want as part of the query. Repeat
Steps 2 and 3 for additional tables if necessary; choose Next.
- 4. If you want to see every record, click the Detail option
button.
- If you want to only see summaries of the records such as totals or counts, choose
the Summary Option button and choose the types of summaries you want; choose
OK.
5.On the next choices in the wizard choose any additional options for grouping
your data and naming your query; choose Finish when done. Save and close your
query.
Query: Delete Field
To remove a field from a query you need to be in Design View. If
that field is needed in sorting or criteria, uncheck the Show box in the design
grid.
Steps
- 1. In the query or filter design grid, click the black down
arrow mouse pointer on the gray field selector above the field name and press Delete.
- 2. To remove all fields, choose Edit, Clear Grid.
Query: Description
If you want to explain your query in more detail than the field name allows, you
can use the Description property.
Steps
- 1. If you are in the Database window, right-click the query name,
choose Properties, and type the text in the Description text box.
2 If you are in Query Design View and the properties window is not in
view, right-click the gray background behind the Field List(s) and choose Properties.
The Query Properties dialog box opens On the General tab type the text in the Description
text box.
Query: Editing
When you want to edit the design of a query, you need to go into Design View.
You might want to add more fields, delete fields, change your criteria, or change
other query properties.
NOTE: You can edit the data in most queries
the same way you edit a table. (See "Data: Edit" in the Database
Essentials part of this book.)
Steps
- 1. On the Query tab of the Database window, select the query
and choose the Design button.
- 2. Select any criteria expression and press the Delete key
to remove it or type a new expression.
- 3. Change a field by clicking the drop-down arrow in the field
box and choosing a different field name.
- 4. If you want to use the same fields but a different table, add
the other table with the Show Table button and change the table name in the design
grid for each field, and then click in the old table Field List and press
the Delete key.
NOTE: If the table name row does not appear
in the Query Design grid, choose View, Table Names.
Query: Format Field
Often when you create a calculation field (see "Calculated Fields:
Queries" in the Calculations part of this book), the data is not formatted the
way you might want. You might also want to change the format of a field to be different
than the Format property of the field in the underlying table. (See
"Data: Format" in the Table and Database Design part of this book.)
Steps
- 1. In Query Design View, right-click the field you want to format
and choose Properties.
- 2. Click in the Format property text box and choose one of the
formats from the drop-down list.
- You can also type customized formats in the Format box. Some examples include:
For a required text character, type @ and the required text characters. For
example, if you want the word Unknown displayed when there is nothing
typed in the field, or No Value when two quotes have been typed in the field,
type @;"Unknown";"No Value"
- For dates, type mmm, or mmmm to see just the month (abbreviation
or full name) or yyyy to see the year.
NOTE: For text you can also type >
to change the entry to be all caps or < for all lower cases. For a description
of customized format characters, click in the Format property and press F1.
Query: Hide and Show Columns
Sometime you will need to use a field but not want to see it displayed
in the query datasheet.
Steps
- 1. If you are in Datasheet View, right-click the column and choose
Hide Columns from the shortcut menu.
- To redisplay the column choose Format, Unhide columns, pick the
column, and choose Close.
- 2. If you are in Design View, uncheck the Show box in the design grid.
Recheck the box to display the column again.
Query: Move Column
You can move a column either in the Datasheet View or Query Design View.
Once you've changed the column order in Datasheet View for a query, you need to change
it in Datasheet View each time rather than depend on Query Design View.
Steps
- 1. In either Design View or Datasheet View, move the mouse to the column
or field header until the mouse pointer becomes a black down arrow. Click and release
the mouse to select the column.
- 2. With the mouse pointer still on the column header (it is now a white
arrow), drag the column to the new location.
Query: Properties
There are a number of useful properties that you can set as part of a query.
These properties help you control the number of records returned in your view
of the result set, optimize performance, and specify other useful options.
Steps
- 1. Open a query in Design View.
2. Click a field in the Field row, a Field List in the data environment,
or elsewhere on the Design View window to select the query.
3. Click the Properties button on the toolbar to display the Property
sheet for the selected object.
4. Add your properties or expressions; then close the Property sheet.
Both the query and field have a Description property for notes or comments. The
field also has a Caption property that shows as the column header or becomes
the default label for forms and reports. The Format property on the field
changes the way data is displayed. (See "Query: Format Field.") The field's
Input Mask property works the same as for fields in a table. (See "Input
Mask: Phone Number and Other Entries" in the Table and Database Design part
of this book.)
Query: Run
You can run a query from Query Design View or directly from the Database window.
CAUTION: Be careful when you run action
queries--they will update or delete data. These queries are indicated with an exclamation
point (!) as part of their icon in the Database window.
Steps
- 1. From the Query tab of the Database window, double-click the query name
to run it.
- 2. From the Query Design View, click the Datasheet View button to show
the results of a select query or preview the results of an action query.
- 3. From the Query Design View, click the Run button to execute the action
query. This button functions the same as the Datasheet View button for select queries.
- 4. From Datasheet View, if you want to requery (perhaps you want to redisplay
the input box for a parameter query), press Shift+F9.
Query: Show All Fields
No matter which fields are used in the grid, a filter shows all
fields. When a filter is saved as a query, all fields also display.
When you create a new query through Design View or through a wizard, only
those fields you use in the grid (and that have the Show box checked) display in
Datasheet View.
Steps
- 1. Select the query in the Database window and choose the Design
button.
- 2. If the Properties window is not displayed, right-click the gray background
behind the Field Lists(s) and the title bar of the query, and choose Properties.
The Query Properties sheet opens.
- 3. Change the Output All Values property to Yes.
Query: Sort Columns
You can apply a sort order to the result set of a query in either Form
or Datasheet View. You can also sort the result set of a filter or an advanced
filter/sort.
The sort order for a query is usually determined by the Sort row of the Query
Design grid. You can sort the query independent of the property, however,
with the following steps.
Steps
- 1. Click the column(s) or field(s) you want to sort on.
- 2. Click either the Sort Ascending or Sort Descending buttons on the toolbar.
NOTE: If your sort is more complex (with multiple
non-adjacent fields) choose Records, Filter, Advanced Filter/Sort
and create your filter.
The primary sort key is the leftmost sorted column in the Query
Design grid. A sort order is saved with the datasheet or form and reapplied
when you open it.
To remove the sort, go into Design View for the query, click the right
mouse on the gray background behind the Field List(s) and choose Properties.
The Query Properties sheet opens. Delete the text in the Filter By property.
SQL Statements: View
SQL stands for Structured Query Language. This is the underlying
language that Access uses to create its queries, filters, and select data for reports.
Most of the time you do not have to see the actual SQL statements because Access
builds them for you in design grids. In a few cases you have to work with SQL if
you want to perform some features of Access (see "Tables: Combine with Union
Query" especially).
Steps
- 1. Open a query in Design View.
- 2. Click the View button on the toolbar and choose SQL View. The
SQL window opens where you can copy or edit the text instructions for the query.
Keywords in the query include the type of query (SELECT, DELETE or, for Append,
INSERT INTO), source of data (FROM), criteria (WHERE), and sort (ORDER BY). Fields
are indicated by the name of their table, a period, and their field name. If field
names include spaces, they are enclosed in square brackets.
Subqueries: Create
Once in awhile you might want to create a query that uses the results of
another query to run. One option is to create a query and then use that query as
the basis for the next query. Another option is to copy the SQL statement
and place it in a Criteria cell.
You might need to find the average price for all products and then find all products
that exceed the average price.
Steps
- 1. If you want to have both query and subquery listed in the Database
window, highlight the first query name in the Database window, click the New
Object button and choose Query. Create the second query using the first queries fields.
- 2. If you want to have only one query in the Database window, first create
the subquery with only one field and one record as an output (create a Totals
Query with no grouping). Display the subquery in SQL view and copy all text. Create
the main query and move to the Criteria row under the field. Type any operator
(such as =, <, >), then type an open parenthesis, paste
the SQL statement, and then type a close parenthesis.
Table Names: Display
When you are designing a query with multiple tables, the grid can be confusing
unless you have table names displayed.
Steps
- 1. In Query Design View, choose View, Table Names
to turn the names on or off.
Tables: Combine with Join
When you have two tables that are related to each other (for example, if you have
Customers and Orders tables), you might want to see fields from both tables. In the
Order table you have a Customer ID but no other customer information. To see the
customer name and phone, as well as order information, you can join two tables
together in a query.
Steps
- 1. From the Design View of a query, click the Show Table
button to add additional table(s), double-click the child table(s) you want
and choose the Close button.
- Access will use any relationship you created in the Relationships window.
- 2. To create a relationship between two tables, drag the field
of the parent tables to the related field of the child tables. A line appears
between the two tables. Generally, you will be dragging the primary key from
the parent table (which is in bold) to a non-primary field in the child table.
- 3. To set the join properties, right-click the line and choose
_Join Properties.
- 4. The default is the first choice, which only shows records where
both joined fields have data. (This is called an Inner Join.)
- Choose the second choice to see matched records and all records from the parent
table without a match in the child table. (This is called a Left Outer Join.)
- Choose the third choice to see matched records and all records from the child
table without a match in the parent table. (This is called a Right Outer Join.)
- 5. Choose OK and finish building the query by adding any fields, sort
options, and criteria you want.
- 6. Click the Run button to run the query.
NOTE: If you design your database relationships
(see "Relationships Between Tables" in the Table and Database Design part
of this book ) with referential integrity checked, the Right Outer Join
should yield the same results as the Inner Join because there should be no "orphan"
records in the child table.
TIP: To find all records that have no
match, create a query with both linked fields and choose a Left Outer Join or Right
Outer Join. Under one of the fields set the criteria to Is Null. You might
want to create this type of query before you create relationships with existing data
to see which records you need to delete.
Tables: Combine with Union Query
The join query in the "Tables: Combine with Join" task
combines related records and adds additional fields when you have an
additional table. In some cases, you do not have related records and yet all
fields of two tables are identical. This can be the case when you have a current
table and an historic table. When you combine the two tables you want to see one
record set below the other. You do this through the Union Query, which must
be written in SQL. Luckily, you can use the Query Design View and then
paste the results.
Steps
- 1. Create a query with the first table. Use field names that are the same
in both tables. Click the View button and choose SQL View.
- 2. Select the entire SQL statement if not already selected, and click
the Copy button.
- 3. Close the query without saving it. Create a query with the second table
and the matching fields. Click the Design View button and choose SQL View.
- 4. Move to the end of the SQL statement and press Enter. Type UNION.
Press Enter again and click the Paste button to paste the SQL statement from the
first query.
- 5. Click the Run button to run the query.
NOTE: If there are any duplicate records
(all fields), they do not display in a Union Query.
Tables: Create a Self Join
When you have a field in the database that refers to another field
in the same database, you can create a self-join where you join the database
to itself to display the information you want. This can occur, for example, when
you have an employee database where one field is the employee ID of the employee's
supervisor and the supervisor is in the same database. Instead of the supervisor's
ID, you want to see the supervisor's name.
Steps
- 1. In Query Design View, select all the fields you want from the
table.
- 2. Click the Show Table button and double-click the same table
so you have two Field Lists from the same table; click Close to return to
the design grid.
- 3. The second table name is identical to the first table name with the
number 1 added. To avoid confusion, right-click in the second Field List and enter
a name in the Alias property to give your second table a name.
- 4. Drag from the field in the first table that is related to the field
in the second table to create a join. These fields will be different names but have
the same data type.
- 5. Finish building the query by adding any fields, sort options,
and criteria you want.
- 6. Click the Run button to run the query.
Top Value Query
If you want to see the top (or bottom) five salespeople or top 10% of customers,
you can create a Top Value query.
Steps
- 1. Create a query with all fields you want to see, especially the field
that you will use to rank the records. Go to Design View of the query.
- 2. Click in the ranking field. For top values, choose Ascending in the
Sort row. For bottom values, choose Descending in the Sort row.
- 3. Click the Top Values button and choose one of the items in the list
or type a value if it is not one of the drop-down choices.
- 4. Click the Run button to run the query.
Unmatched Query
As you are designing your database you might need to check how data from
two existing tables are related. This will be especially true if you plan
on creating a relationship between the tables and enforcing referential
integrity. (See "Relationships between Tables" in the Table and Database
Design part of this book.)
Steps
- 1. In the Query tab of the Database window, choose the New
button and choose Find Unmatched Query Wizard.
- 2. Double-click the table where the parent records are located.
- 3. On the next screen, double-click the table where the child records
are located.
- 4. On the third screen, choose the name of the related field in both lists
and choose the <=> button; choose Next.
- 5. On the next two screens, choose which fields you want to display and
the name for your query; choose Finish to create the query.
The query will only display records from the first table because there aren't
any related records from the second table. You can edit these records or delete them
from this query. (See "Records Delete" in the Database Essentials part
of this book.)
|
