Problems with Delete queries in MS access 2007

[msowards]

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?

 

[dhookom]

Access queries containing GROUP BY are not updateable. You could change this to an append query to append a primary key value to a temporary table. Then use the temporary table in your delete query.

There might be a solution using a totals query in the criteria.

Duane
Hook'D on Access
MS Access MVP

 

[msowards]

Thanks. I kind of thought there was something like that going on. If I remember correctly the Group On is in the totals row of the Access query design.

 

[PHV]

I'd *try* something like this:
DELETE A.*
FROM Table_A A
WHERE A.date_updated<(SELECT Max(date_updated) FROM Table_A WHERE FLD4=A.FLD4 AND FLD3=A.FLD3 AND FLD2=A.FLD2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[msowards]

That did it. This query produces the proper list and Access allows it to delete the records.

Thanks