May 17, 2021 MS Access
In this tutorial, we created a variety of selection queries, mainly from the design view. b20> ry. he Find Duplicates Query Wizard creates a query to find records with duplicate field values in a single table or query.
As we've discussed, relationship databases are designed to avoid storing duplicate information. But despite the design, sometimes users accidentally enter duplicate information.
In the customer table, you can accidentally add the same customer twice. I n this case, the customer will have the same address, but a different customer ID, which may cause reporting problems. b20> In this case, you can use the repeat query wizard to quickly find possible duplicate entries.
Now let's open the Access database with the tblAuthers table, go to the Create tab, and select the query wizard in the query group.
If you see the following screenshot, you have four different wizards to choose from. Select the Find Repeat Query wizard, and then click OK.
The first screen of the Find Repeat Query wizard asks what tables you want to search for or what queries might duplicate. 20> o, select tblAuthors, and then click Next.
The second screen in the wizard asks which fields might contain duplicate information.
Typically, you won't use your primary key field because Access won't allow duplicate input when you specify a field as the primary key in Access.
We'll look at any other fields or combinations of fields.
We can search by last name or first name and last name, or you can search by their street address, or more specifically use their phone number or birthday.
Now let's search by first name, last name, and birthday, and then click Next.
The following screen in this wizard will ask for the fields we want to display in our query. ts.
Now, let's click Next . rd.
In the following screen, enter how you want to name the query.
By default, it names it to find a plus sign for repeated , regardless of the name of the object you are querying. In this case, tblAuthors, but you can give it any other name and then click Done.
Here, Access finds a possible repetition, which will be that author Jose Caline has the same birthday, the same address, the same phone number, but a different AutoID.
Of these, it must have been entered twice by accident. le. e must also ensure that we do not have any relevant records in another table.
Select any record, and then select Delete in the dialog box below.
Access prompts you, "You're about to delete a record." "If you want to continue, click Yes."
If you want to see how the wizard creates this query, go to the design view and see what has been added to this query.
As you can see in the screenshot above, we have our fields and some specific criteria below the first field.
This is how this wizard is looking for duplicate information. This is by far the easiest way to find repetition.