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
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:
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:
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.