Hey all,
I have finally got a Query that finds the duplicate records( based on three different columns) and list all the earlier duplicates (based on transaction date) for deletion.
When i was developing this Query, with outside help, I tested it by using the 'Datasheet View'. It produced a list of records to delete which meet my expectation, nut when i went to Execute the query Access errors out saying, "Could not delete records from specified tables."
DELETE Table_A.*
FROM Table_A INNER JOIN (SELECT max(date_updated) AS LatestUpdate, Table_A.FLD2, Table_A.FLD3, Table_A.FLD4 FROM Table_A GROUP BY Table_A.FLD2, Table_A.FLD3, Table_A.FLD4
HAVING count(1)>1) AS B
ON (Table_A.FLD4=B.FLD4) AND (Table_A.FLD3=B.FLD3) AND (Table_A.FLD2=B.FLD2)
WHERE (((Table_A.date_updated)<.[LatestUpdate]));
This is the Query produces a correct list but won't delete. Any ideas?
I have finally got a Query that finds the duplicate records( based on three different columns) and list all the earlier duplicates (based on transaction date) for deletion.
When i was developing this Query, with outside help, I tested it by using the 'Datasheet View'. It produced a list of records to delete which meet my expectation, nut when i went to Execute the query Access errors out saying, "Could not delete records from specified tables."
DELETE Table_A.*
FROM Table_A INNER JOIN (SELECT max(date_updated) AS LatestUpdate, Table_A.FLD2, Table_A.FLD3, Table_A.FLD4 FROM Table_A GROUP BY Table_A.FLD2, Table_A.FLD3, Table_A.FLD4
HAVING count(1)>1) AS B
ON (Table_A.FLD4=B.FLD4) AND (Table_A.FLD3=B.FLD3) AND (Table_A.FLD2=B.FLD2)
WHERE (((Table_A.date_updated)<.[LatestUpdate]));
This is the Query produces a correct list but won't delete. Any ideas?