delete duplicate max ID

  • Thread starter Thread starter Opal
  • Start date Start date
O

Opal

I am confused. I want to delete
duplicate values from my table in
access 2003 and I tried Allen Browne's
subquery:

DELETE FROM ZoneOrg
WHERE ZoneOrgID <> (SELECT Max(ZoneOrgID) AS MaxOfZoneOrgID
FROM ZoneOrg AS Dupe
WHERE (Dupe.TMNumber = ZoneOrg.TMNumber));

but I get an error:

"Query must have at least one destination field"

What am I doing wrong?
 
I am confused. I want to delete
duplicate values from my table in
access 2003 and I tried Allen Browne's
subquery:

DELETE FROM ZoneOrg
WHERE ZoneOrgID <> (SELECT Max(ZoneOrgID) AS MaxOfZoneOrgID
FROM ZoneOrg AS Dupe
WHERE (Dupe.TMNumber = ZoneOrg.TMNumber));

but I get an error:

"Query must have at least one destination field"

What am I doing wrong?
Click to expand...

Two problems: one, you need to specify what to delete (DELETE * is the best
bet); and secondly, the MAX will probably make the query non-updateable. Try

DELETE * FROM ZoneOrg
WHERE ZoneOrgID NOT IN
(SELECT Max(ZoneOrgID) AS MaxOfZoneOrgID
FROM ZoneOrg AS Dupe
WHERE (Dupe.TMNumber = ZoneOrg.TMNumber));
 
Thank you, John.

That did the trick...

Perhaps Allen should make that more
clear on his website.
 
Back
Top