Duplicates

[kirkm]

Could someone assist with a query to show duplicates in Fields E & F combined, plus fields A, B and C Where id > 666

Maybe other Where conditions but I can probably work them out OK. Tried the query wizard but couldn't seem to get the right result.

Thanks.

 

[plog]

Could someone assist with a query to show duplicates in Fields E & F combined, plus fields A, B and C Where id > 666

Explain this better please. Actually demonstrate with date. Provide 2 sets:

A. Starting data from your table. Include table and field names and enough data to cover all cases.

B. Expected results based on A. Show what data you expect your query to produce when you use the data from A.

 

[kirkm]

Well, there's data in cols A, B, C, E. F. It doesn't matter what (it can be anything). Output from all these fields is wanted where duplicates exist in joined E + F . The id column is an autonumber and 666 is just an arbritary value, as it will vary.

 

[plog]

Here's a query that will identify all E & F permutations that have more than one record above ID=666:

SELECT E, F
FROM YourTableNameHere
WHERE ID>666
GROUP BY E, F
HAVING COUNT(ID)>1;

Save that query, then build a new one using it and Your Table linking them via E & F fields and you can pull back the full records from Your table that are duplicates.

 

[arnelgp]

SELECT A, B, C, E, F FROM table1 WHERE ((ID) > 666) AND ((A & B & C ) = (E & F));

If you want a rumble of all fields in A, B, C:

SELECT A, B, C, E, F FROM table1 WHERE ((ID) > 666) AND (E & F) IN ((A & B & C), (A & C & B), (C & A & B), (C & B & A), (B & A & C), (B & C & A));

if you need to further rumble E & F:

SELECT A, B, C, E, F FROM table1 WHERE ((ID) > 0) AND ((E & F) IN ((A & B & C), (A & C & B), (C & A & B), (C & B & A), (B & A & C),(B & C & A))) OR ((F & E) IN ((A & B & C), (A & C & B), (C & A & B), (C & B & A), (B & A & C),(B & C & A)));

 

[kirkm]

Thanks for the replies. Plog, your example was spot on. A count was my next stage and I was able join it to the main table and achieve full success.

I tried the first 2 of anelgp Select queries but nothing was output. Fields E & F aren't contained in the other fields, so maybe that is correct. I'd never seen "IN" before and wasn;t sure what the commas were. So much to this...

My own attempt failed when I tried calculated fields E & F. Although it sort of worked if I ran a make table query first. But not the best solution.