Duplicate Query for Two Tables

[bioinformatic]

Hi all,

I am working on a rice and barley bioinformatics project using ACCESS at UC Berkeley. I've run into some trouble when building a query to find duplicate names in two tables.

The Duplicate Query Wizard built into Access can only look for duplicates in the same table. Does anyone know of any way to search for duplicates in two tables?

I appreciate your help.

Thanks,
Randy W.

 

[tlbroadbent]

If you just want to match tables by key values to find duplicates, use a JOIN query.

Select TableA.Key1, TableA.Key2, ...
From TbaleA Inner Join TableB
On TableA.Key1=TableB.Key1
And TableA.Key2=TableB.Key2
.
.
.

If you want to find exact duplicates for a whole record use a UNION subquery and and an aggregate count.

Select key1, key2, key3, ...
From (
SELECT * FROM tableA
UNION
SELECT * FROM tableB) As u
Group By key1, key2, key3, ...
Having count(*) > 1; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bioinformatic]

Thanks Terry. I'll give it a try.

-Randy