delete duplicate max ID

[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?

 

[Jeff Boyce]

One possibility:

DELETE ?what? FROM ...

Regards

Jeff Boyce
Microsoft Access MVP

 

[John W. Vinson]

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?

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));

 

[Opal]

Thank you, John.

That did the trick...

Perhaps Allen should make that more
clear on his website.

 

[Jeff Boyce]

Let him know.

Regards

Jeff Boyce
Microsoft Access MVP