By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,644 Members | 2,423 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,644 IT Pros & Developers. It's quick & easy.

Find duplicates in another table

Expert 5K+
P: 8,435
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.
Oct 22 '06 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT F1, F2
FROM Table1 INNER JOIN Table2
ON (Table1.F1=Table2.F1 AND Table1.F2=Table2.F2);
Oct 22 '06 #2

Expert 5K+
P: 8,435
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.
Oct 23 '06 #3

Expert 5K+
P: 8,435
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.
Oct 23 '06 #4

Expert 5K+
P: 8,435
P.S. I was always a bit vague on JOINs. Time to do some reading...
Oct 23 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.