Link to home
Get AccessLog in
Avatar of Conernesto
ConernestoFlag for United States of America

asked on

Access Database table with duplicate data

I have a table called MonthlyRateChanges. The table has 8 fields. Below is sample of my data. The first row are the field names. I want to delete records that are the same based on the first six fields. The two records below are the same for the first six fields. How can  I delete the whole record if the records are the same? From below both records would be deleted as the first six fields are the same.


Zip             City              County             State Combstax      Combutax      Effdate           Change
36061      BANKS      BULLOCK      AL      0.065000      0.065000      20091001      D
36061      BANKS      BULLOCK      AL      0.065000      0.065000      20191001      I
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

One method is to use a temp table:

SELECT Zip, City, County, State, Combstax, Combutax INTO Dublets
FROM YourTable
GROUP BY Zip, City, County, State, Combstax, Combutax
HAVING Count(*) = 2;

Open in new window

Then delete while filtering on this:

DELETE
FROM Pay
WHERE Zip & "*" & City & "*" & County & "*" & State & "*" & Combstax & "*" & Combutax 
IN (Select Zip & "*" & City & "*" & County & "*" & State & "*" & Combstax & "*" & Combutax From Dublets)

Open in new window

Avatar of Conernesto

ASKER

Hi,

I am not sure how to do above. Attached is a copy of the database with the actual table. Any assistance you can provide will be greatly appreciated.
MonthlyRateChanges.accdb
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Thank you very much for your help.
You are welcome!