Link to home
Create AccountLog in
Avatar of Frank Freese
Frank FreeseFlag for United States of America

asked on

Delete dups in table

Folks,
My tblGJAccountNumber was imported from an Excel spreadsheet with duplicate Account Numbers. The table looks like this:
GJAccountNumberId (PK) autonumber
GJ_Account_Number (number)
I found the duplicates from a query and now I would like to delete the duplicates: here's the query

SELECT First(tblGJAccountNumber.[GJ_Account_Number]) AS [GJ_Account_Number Field], Count(tblGJAccountNumber.[GJ_Account_Number]) AS NumberOfDups
FROM tblGJAccountNumber
GROUP BY tblGJAccountNumber.[GJ_Account_Number]
HAVING (((Count(tblGJAccountNumber.[GJ_Account_Number]))>1));

Avatar of Sheils
Sheils
Flag of Australia image

Delete First(tblGJAccountNumber.[GJ_Account_Number]) AS [GJ_Account_Number Field], Count(tblGJAccountNumber.[GJ_Account_Number]) AS NumberOfDups
FROM tblGJAccountNumber
GROUP BY tblGJAccountNumber.[GJ_Account_Number]
HAVING (((Count(tblGJAccountNumber.[GJ_Account_Number]))>1));
MS can do this for you. Just right click on the query in design view. In the popup dialog box click Query Type>Delete Query
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Frank Freese

ASKER

BANG - you nailed it cap
great job! thank you