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

Last Comment

Gustav Brock

8/22/2022 - Mon

Gustav Brock

12/4/2019

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

12/4/2019

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

12/4/2019

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