RE: retrieving duplicate records from multiple tables

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


This might point you in the right direction.

SELECT ItemNumber, ItemName,Count([ItemName]) as TheDupes,
Min(TheTest) as FirstTable, Max(TheTest) as LastTable
FROM (SELECT
Test1.ItemNumber, Test1.ItemName, "TEST1" AS TheTest
FROM Test1
UNION ALL
SELECT Test2.ItemNumber, Test2.ItemName, "TEST2"
FROM Test2
UNION ALL
SELECT Test3.ItemNumber, Test3.ItemName, "TEST3"
FROM Test3)
GROUP BY ItemNumber, ItemName
HAVING Count([ItemName])>1 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"John B. Smotherman" wrote:

I'm writing a resource scheduler and am currently testing a duplicate finder.

I have three tables, Test 1, Test 2, Test3. The fields these three tables
contain are the same: ItemNumber, ItemName, MFR, Model, Serial

I've created a query to find duplicate item numbers in the three tables, but
what I get is the intersection of the comparison between Test1 and Test3 and
the comparison of Test2 and Test3. What I need is the full results of both
comparisons.

Here's the SQL:

SELECT Test3.ItemNumber, Test3.ItemName FROM (Test3 INNER JOIN Test1 ON
Test3.[ItemNumber] = Test1.[ItemNumber]) INNER JOIN Test2 ON
Test3.[ItemNumber] = Test2.[ItemNumber];

What change do I need to make? Thanks
.

Relevant Pages