retrieving duplicate records from multiple tables

[John B. Smotherman]

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

 

[Jerry Whittle]

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 ;