Avatar of Conernesto
Conernesto
Flag 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
Databases

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
Gustav Brock

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

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
Gustav Brock

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Conernesto

ASKER
Thank you very much for your help.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Gustav Brock

You are welcome!