CODEWITHSUNDEEP
ms-accessms-access-2003
Hell_77
Hell_77

Reputation: 69

MsAccess - How to find duplicate records only

I have table -

Tbl1

ID       Sort   Amount Price  Address
000123   AAA    135.00 25.00  25Grant
000123   AAB    135.00 15.00  27Pent
000124   AAC    118.00 16.00  47Bay
000125   BBB    116.00 17.00  56Third
000125   BBB    116.00 66.50  56Third

I need to select only duplicate records for fields ID, Sort, Amount. I don't need to select Price and Address fields.

I am expecting result:

ID      Sort  Amount
000125  BBB   116.00 
000125  BBB   116.00

I know I can use "Duplicate Records query" in MsAccess query wizard. But I need to understand how do I do it in SQL.

When I viewed "Design" or "SQL" view I still not completely understand the algorithm.

If someone could write -finding duplicates query- here with the explanation, I'd be appreciate...

Upvotes: 1

Views: 12506

Answers (1)

Marc B
Marc B

Reputation: 360782

SELECT ID, Sort, Amount, COUNT(*) 
FROM yourtable
GROUP BY ID, Sort, Amount
HAVING COUNT(*) > 1

Upvotes: 5

Related Questions