Access Database table with duplicate data

Conernesto
Conernesto used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Gustav BrockCIO
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Author

Commented:
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
CIO
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
The queries will be:

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

Open in new window

and:

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

Open in new window

Save them as q1 and q2.
Then execute q1 first, then q2.
MonthlyRateChanges.mdb
Conernesto

Author

Commented:
Thank you very much for your help.
Gustav BrockCIO
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial