To Access Page

Query Wizards

 

Creating Microsoft Access Queries using the Query Wizards:

We can use the Microsoft Access Query Wizard to help create Crosstab Queries, as shown in a previous tutorial on Creating Crosstab Queries in Microsoft Access. In Access there are two other query wizards that are available to maintain the database, and these are:

The Find Duplicates Query Wizard works on a single table, whereas the Find Unmatched Query Wizard compares records in one table with records in another.

These query wizards, along with the Crosstab wizard as listed when you choose New Query in when viewing the Query Objects in the database window:

Showing the Find Duplicates/Find Unmatched Query Wizards, when choosing New Query
Showing the Find Duplicates/Find Unmatched Query Wizards, when choosing New Query

Find Duplicate Query Wizard

By using this query wizard, you can create a query that will allow you to search for records in a single table that are duplicated, based upon a field or fields. You specify in the wizard which fields that you want to use for checking duplication and you can also display further fields in your query results.

This wizard can help locate duplicate key violations - a good trick to use when you want to take an existing database table and make a unique (primary) key field with the existing data. If you try to create a unique key field and Access reports an error, you know that you have either duplicate records in the table or you have Null values in the field.

If using the Microsoft Access Find Duplicates Query Wizard still doesn't help you find what you need, you may want to check out the following product, which overcomes some of the shortfalls in the wizard:

Duplicates Finder | Microsoft Access Add-in

This Microsoft Access add-in is your ultimate tool to find, edit and delete duplicate records from your table. It is simple and easy to use yet powerful, versatile and comprehensive enough to be your one-stop resource as well as a real productivity-enhancing device. This utility works like Access' Find Duplicates Query Wizard(FDQW) while overcoming FDQW's shortcomings and expanding FDQW's functionality. It gets the job done quickly and efficiently. You need Access 2000 or higher to run this utility.

Check out Duplicates Finder's Key Features:

  1. Duplicates Finder works like Access’ Find Duplicates Query Wizard. You can use this five step self-explanatory add-in with no training.
  2. With Find Duplicates Query Wizard, you just get all your duplicate records, making it hard to distinguish among different instances of the duplicate records in each duplicate set, when you are trying to edit or delete your records if your table does not include a unique numeric field. Duplicates Finder automatically adds an AutoNumber field to your table if one is missing, enabling you to easily identify particular instances of duplicates.
  3. Duplicates Finder provides 12 different ways to query your table, facilitating your task of identifying, editing, and removing all duplicate records from your table quickly and efficiently.

  4. Find Duplicates Query Wizard also comes in handy when you need to identify the first or last order by each customer (Use a customer name field as a duplicate field).
  5. You can either get your query results in a datasheet view or make a table out of the results or delete your filtered data.
  6. You can adapt the 12 query techniques used for this add-in to your own needs.
  7. Duplicates Finder overcomes a Find Duplicates Query Wizard’s shortcoming: With Find Duplicates Query Wizard, if you do not select additional fields to display on top of your duplicate fields, you end up getting a list of first instances of duplicates as opposed to all instances of duplicates! With Duplicates Finder you do not have to select additional fields.

Find Unmatched Query Wizard

This query wizard will allow you to create a query that reports any orphan or widowed records between two Microsoft Access tables.

An orphaned record is when a record in the many-side (child or related) table relationship has no corresponding record in the one-side (parent or primary) table. An example of this may be a Product in the Products table that does not have a Supplier in the Suppliers table, therefore the Product is an orphan record.

A widowed record is when the record in the one side of a one-to-many or one-to-one table does not have a corresponding record in the other table. You may have a Supplier who has no Products in the Products table.

You create the query, naming the two tables that you want to compare. You will need to specify the linking field between the two tables.

Using this query can assist in finding records that have no corresponding values in other tables. If, when you create a relationship between two Microsoft Access tables and try to Enforce Referential Integrity, Access reports that you cannot, it may be due to violating integrity. This query will help resolve this matter by locating the violating records.