MS Access How can I find dupes for 3 fields from 2 tables?

I have 2 tables with the same field names. How can I produce a query that displays all the dupes for table 1?
Fields: (in both tables)
Matnum, Subnum & Catnum
Only 1 field needs to match.

My method:
Created 3 queries - each have a one to one relationship (only include rows if both fields are equal)
(Display only table 1 values)
Matnum - Matnum
Subnum - Subnum
Catnum - Catnum

How can I include all these queries in one query or one listing/table with no dupes? (dupes meaning ID for table 1)
Or is there a better method?
Please be advised these tables are large (over 10K records).
DJPr0Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
can you show few rows from the tables and the expected output.

Also can you show your queries?
Gustav BrockCIOCommented:
You can run one query:

Select Distinct
    T1.*, T2.*
From 
    Table1 As T1, Table2 As T2
Where
    T1.Matnum = T2.Matnum
    Or
    T1.Subnum = T2.Subnum
    Or
    T1.Catnum = T2.Catnum

Open in new window

/gustav
Fabrice LambertConsultingCommented:
Can a union query perform the job ?
SELECT 	Table1.Matnum As MatNum1, Table2.Matnum As MatNum2,
		Null As Subnum1, Null As SubNum2,
		Null As Catnum1, Null As Catnum2
FROM	Table1 INNER JOIN Table2
			ON Table1.MatNum = Table2.MatNum
UNION
SELECT 	Null As MatNum1, Null As MatNum2,
		Table1.SubNum As Subnum1, Table2.SubNum As SubNum2,
		Null As Catnum1, Null As Catnum2
FROM	Table1 INNER JOIN Table2
			ON Table1.SubNum = Table2.SubNum
UNION
SELECT 	Null As MatNum1, Null As MatNum2,
		Null As Subnum1, Null As SubNum2,
		Table1.CatNum As Catnum1, Table2.CatNum As Catnum2
FROM	Table1 INNER JOIN Table2
			ON Table1.CatNum = Table2.CatNum

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Pawan KumarDatabase ExpertCommented:
Please try like this -

Select *
From 
Table1 As T1 INNER JOIN Table2 AS T2 
ON t1.Matnum = t2.Matnum
UNION ALL
Select *
From 
Table1 As T1 INNER JOIN Table2 AS T2
ON t1.Subnum = t2.Subnum 
UNION ALL
Select *
From 
Table1 As T1 INNER JOIN Table2 AS T2
ON t1.Catnum = t2.Catnum

Open in new window

DJPr0Author Commented:
Pawan Kumar:

Your statement works but how do I eliminate the dupes?
How can I output table 1 dupes only? ( if 1 of the fields match in (table 1 & table 2) display a list of table 1 without the dupes)
Pawan KumarDatabase ExpertCommented:
Please try this -

Please replace column1,column2.....to your actual columns.

SELECT DISTINCT column1,column2..
FROM 
(
	Select *
	From 
	Table1 As T1 INNER JOIN Table2 AS T2 
	ON t1.Matnum = t2.Matnum
	UNION ALL
	Select *
	From 
	Table1 As T1 INNER JOIN Table2 AS T2
	ON t1.Subnum = t2.Subnum 
	UNION ALL
	Select *
	From 
	Table1 As T1 INNER JOIN Table2 AS T2
	ON t1.Catnum = t2.Catnum
) AS k

Open in new window

Gustav BrockCIOCommented:
There will be no double listings.

/gustav
DJPr0Author Commented:
Pawan:

Receiving error:
"Syntax error in from clause"
Gustav BrockCIOCommented:
Then run my simple query with no errors.

/gustav
Pawan KumarDatabase ExpertCommented:
use alias like below and you should be fine..

SELECT DISTINCT K.column1, K.column2..
FROM
(
      Select *
      From
      Table1 As T1 INNER JOIN Table2 AS T2
      ON t1.Matnum = t2.Matnum
      UNION ALL
      Select *
      From
      Table1 As T1 INNER JOIN Table2 AS T2
      ON t1.Subnum = t2.Subnum
      UNION ALL
      Select *
      From
      Table1 As T1 INNER JOIN Table2 AS T2
      ON t1.Catnum = t2.Catnum
) AS k

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DJPr0Author Commented:
Thanks Pawan!
DJPr0Author Commented:
Gustav Brock - your solution produces dupes - your solution may work with some adjustments.

Thanks for your input.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.