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
One method is to use a temp table:
SELECT Zip, City, County, State, Combstax, Combutax INTO DubletsFROM YourTableGROUP BY Zip, City, County, State, Combstax, CombutaxHAVING Count(*) = 2;
DELETEFROM PayWHERE Zip & "*" & City & "*" & County & "*" & State & "*" & Combstax & "*" & Combutax IN (Select Zip & "*" & City & "*" & County & "*" & State & "*" & Combstax & "*" & Combutax From Dublets)
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
Open in new window
Then delete while filtering on this:Open in new window