delete duplicate records without creating a new table?

[Guest]

Hi,

I've been looking through the message board, but I can't seem to figure this
out. I need to delete records that are partial duplicates from a table. I
can easily display the duplicates using the "find duplicates" wizard, but now
I want to delete them, without creating a new table (this is because my table
is involved in multiple relationships, and I don't want to reset them
everytime I need to use this query).

It seems like it should be easy to turn my "select" query into a "delete"
query. I just want to delete all the records from the original table that
are found by my select query.

Can anyone help me out? Thanks in advance.
-Chris

 

[Lynn Trapp]

Can you post your select query here?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

Hi Lynn,
I truly appreciate the fast response. Here's the select query (created using
the wizard). It works just fine.

I tried prefacing it with "Delete test2.* From test2 Where NOT EXISTS" but
that just seems to freeze the program. Maybe my syntax is wrong?

SELECT test2.APN, test2.Updated, test2.APN2, test2.PROP_ADDRESS,
test2.PROP_CITY, First(test2.OWNER_NAME) AS FirstOfOWNER_NAME
FROM test2
GROUP BY test2.APN, test2.Updated, test2.APN2, test2.PROP_ADDRESS,
test2.PROP_CITY
HAVING (((test2.APN) In (SELECT [APN] FROM [test2] As Tmp GROUP BY
[APN],[APN2],[PROP_ADDRESS],[PROP_CITY],[Updated] HAVING Count(*)>1 And
[APN2] = [test2].[APN2] And [PROP_ADDRESS] = [test2].[PROP_ADDRESS] And
[PROP_CITY] = [test2].[PROP_CITY])))
ORDER BY test2.APN, test2.APN2, test2.PROP_ADDRESS, test2.PROP_CITY;

Thanks!