Need to Find Duplicates from Two Different Tables

[Toria]

Hello,

I have two tables in the same file: Table 1 and Table 2. They have
different fields but have one in common: BAN. I need a query to pull
duplicate BAN records from the two tables. The Find Duplicates query isn't
working for me (I get no results).

The reason to find the duplicates is to take information from table 2 (rep
name, phone, etc.) and plug that info into the record on table 1 with the
same BAN. If there's a way to automate that as well, that would be wonderful.

Thank you!!

 

[Klatuu]

The Find Duplicates wizard is used to find duplicate values in one table.
To match duplicates in two tables, create a query and join the two table on
the BAN field. If you select Option 1 in the Join Type, it will return only
rows where there is a matching value in both tables.

 

[Toria]

Thank you. I actually have already done this. I get over 2,000 results. One
table has 800 records and the other 300.

Is there a union query that could be done that would also pull in the
name, phone, etc. from the one table into the other? That's really why I
need to find these duplicates.

 

[Klatuu]

You can't be doing it correctly. The absolute maximum you could get would be
300 and that would be if there were a match in the larger table for every
record in the smaller table.

You have not created a join on the BAN field. What you are getting is every
possible combination. That happens when the tables are not joined.

With your query builder open and both tables showing in the top portion,
click on the BAN field in the first table and drag the cursor to the second
table. It will draw a line between the two fields. Right click on that line
and you will get a message box for Join Type. Select option 1. Sometime you
will get a menu, but that is when you are not exactly on the line.

A Union query would not be useful if you intend to do an update. Get you
query working as a select query, then we can move on to doing the update.

 

[Toria]

Thanks for the info. I've done this and now have a proper amount of results.

 

[Klatuu]

Great. Now you need to change your query to an update query. Remove all the
fields from the query grid except those in table one you want to update with
the values in Table 2. In the Update To row of the query grid, put the name
of the fields from Table 2 you want to update from.

 

[Toria]

Thanks, Dave!!!! I'm good to go!

Great. Now you need to change your query to an update query. Remove all the
fields from the query grid except those in table one you want to update with
the values in Table 2. In the Update To row of the query grid, put the name
of the fields from Table 2 you want to update from.

 

[Klatuu]

Great.
--
Dave Hargis, Microsoft Access MVP

Thanks, Dave!!!! I'm good to go!