access how to find duplicate fields in another table
Create your own query to find unmatched records. 1  On the Create tab, in the Other group, click Query Design. 2  In the Show Table dialog box, double-click the table that has unmatched records, and then double-click the table that has related records. 3  Close the Show Table dialog box. Finding Duplicate Records. An even more common need is to find duplicates in a table. Perhaps you want to know how many duplicate names there are in the table so you can begin to clean up your data. To do so, create a normal Access Select Query containing the field with duplicate information. The change the query to be an Aggregate Query by clicking the Totals icon in the ribbon. Add one more field, any will do, and then set that field to be Count.. This will give you a count of records that match the name. Find duplicate records in a table based on the values of multiple fields. Find duplicate customer orders based on the values of multiple fields. Discussion: In this example, we are looking for duplicate records based on the values of eight fields. In other words, if the values of eight fields of at least two records are identical, those records will appear as duplicates. Use the Find Unmatched Query Wizard to compare two tables. 1  On the Create tab, in the Other group, click Query Wizard. 2  In the New Query dialog box, double-click Find Unmatched Query Wizard. 3  On the first page of the wizard, select the table that has unmatched records, and then click Next. Create and modify a Find Unmatched Query to compare by more than one field. 1  On the Create tab, in the Other group, click Query Wizard. 2  In the New Query dialog box, double-click Find Unmatched Query Wizard. 3  On the first page of the wizard, select the table that has unmatched records, and then click Next. Step 1: add a field in the table that has the records you want to delete called DeleteMe - or something to that effect. Step 2: JOIN these two tables together in a query by whatever field is related so that when you run the query you see only records that are in both. Step 3: convert this query over into an UPDATE query. Now, update the value of the DeleteMe field to YES. Step 4: run the query. Say you want to get a list of all the records that match in two tables or queries. Perhaps you have a list of donors in two queries and want to know how many match up. You can create a normal Access Select Query, then drag the fields you want to match together. For example, First Name, Last Name, and Address. There is a FIND DUPLICATES query, but that only searches for duplicate RECORDS in a single table... which you could do, if you could merge both tables together and then delete all of the duplicates. Here's my cheap workaround for finding duplicate records between two tables. Steps. 1  1. Know what makes data duplicate. 2  2. Backup your database. 3  3. Inform other users that you're about to make changes.  4. Open the Query 1  Wizard. 5. Select the Find Duplicates Query Wizard.  6. Choose the table you want to 1  search. 7. Select the fields you think contain duplicates.  8. Select additional fields 1  to view. 9. Create the  query. Find duplicate customer orders based on the values of multiple fields. In this example, we are looking for duplicate records based on the values of eight fields. In other words, if the values of eight fields of at least two records are identical, those records will appear as duplicates.
Create a normal Access Select Query containing the field with duplicate information. The change the query to be an Aggregate Query by clicking the Totals icon in the ribbon. Add one more field, any will do, and then set that field to be Count.This will give you a count of records that match the name.