Find duplicates in another table

[Killer42]

Hi all.

Sorry, this is probably a really simple one but I'm having some difficulty with it, and don't have much time to devote to it right now.

I need to find all the records which have duplicates (on two fields) in another table. In Access 2003, the "Find duplicates wizard" will produce a query to list duplicates within the one table, but what about when they're in a different one?

In other words, I want to select all records in table T1 who have values in fields F1 & F2 which are duplicated in table T2 (same fields).

So far I have tried throwing together a query in the designer, and created a field there "[UniqueValue]:[F1] & [F2]". In the criteria I put "In (Select ([F1] & [F2]) AS [UniqueValue] From T2)". Have tried running this, but don't know whether it works. It has been running for half an hour or so, and I don't know how long it might take. I'll have to try it with a smaller number of records (currently millions in both tables), but any pointers would be appreciated.

[MMcCarthy]

SELECT F1, F2
FROM Table1 INNER JOIN Table2
ON (Table1.F1=Table2.F1 AND Table1.F2=Table2.F2);

[Killer42]

SELECT F1, F2
FROM Table1 INNER JOIN Table2
ON (Table1.F1=Table2.F1 AND Table1.F2=Table2.F2);

Thanks, I'll give it a shot.

[Killer42]

Thanks, I'll give it a shot.

I gave it a shot. :)

It worked like a dream, and I have now modified it to make a Delete query, which is what I had in mind.

Thanks heaps, mmccarthy.

[Killer42]

P.S. I was always a bit vague on JOINs. Time to do some reading...