Advance Queries System -
- The Find Duplicates Query Wizard creates a Select query that locates
duplicate records in the same table.
- The Find Unmatched Query Wizard creates a Select query that locates
records in one table that don't have related records in another table.
- The Crosstab Query Wizard creates a type of Select query that summarizes
data by catagories
- Parameter queries prompt you to enter criteria when you run the query.
As a result, you can reuse the same query for a number of results.
- Action queries act on data by inserting new data, modifying existing data,
and deleting records.
Note |
If you need to base a crosstab query on more than one table, you must create
a query that combines the tables and work from that. Considering the
results, this isn't such a big deal. You just need to be aware of the
requirement.
Note |
Access also supports an append query (which adds records to an existing
table) and a make table query (which makes a brand-new table). Both are
considered action queries, but we would not review them in this book.
Caution |
Before executing any action query, take two steps to
protect your data. First, make a copy of the table that will be changed.
If the query doesn't return the appropriate results, you still have a copy of
the unchanged data. Second, run the action query as a select query.
This lets you see which records will be changed without actually making the
change.
Note |
Access can handle more than one parameter expression in the same query.
For instance, you could add a second prompt that returns only medicinal plants
purchased from United States catalogs by adding the parameter expression [Enter
a TypeID value] to the TypeID field's Criteria cell.
Caution
You can ignore a parameter prompt, but there's not
much point to that. You might expect the query to return all the records
because you're not limiting the records in any way, but that's not how Access
interprets the empty prompt. If you leave the prompt empty, Access most
likely won't return any records. Access thinks you want to see those
records that are null (empty), and because none of the Country fields are
blank, the query fails to match any records and returns nothing