query help

[Guest]

I used a find duplicates query to locate some duplicate records in my table.
There were 6 duplicate records. I saved the find duplicates query for later
use. Now I need to create a delete query based on the results of the find
duplicates query to delete the duplicate names and adresses. I opened my find
duplicates query in design view and changed the query type to a delete query.
Instead of deleting the duplicate files, it wants to delete every record in
the table. How can I delete only the duplicate records while keeping the
unique ones?

Thanks for any insights you might have

 

[Ken Snell \(MVP\)]

You'll need to create a query similar to this:

DELETE * FROM TableWithDupRecords
WHERE TableWithDupRecords.PrimaryKey
IN (SELECT T.PrimaryKey
FROM MainTable AS T
INNER JOIN TTableWithDupRecords AS W
WHERE T.LinkingFieldName =
W.LinkingFieldName);


The subquery will be similar to the query you created to find the
duplicates. Its purpose above is to get the list of primary key field values
that are duplicates in the TableWithDupRecords so that the delete query can
delete just those records.

 

[Guest]

I used a find duplicates query to locate some duplicate records in my table.
There were 6 duplicate records. I saved the find duplicates query for later
use. Now I need to create a delete query based on the results of the find
duplicates query to delete the duplicate names and adresses. I opened my find
duplicates query in design view and changed the query type to a delete query.
Instead of deleting the duplicate files, it wants to delete every record in
the table. How can I delete only the duplicate records while keeping the
unique ones?

Thanks for any insights you might have

Thank you for the help. Should I do this is sql view? Thanks again

Tom

 

[Ken Snell \(MVP\)]

Probably easier to do in SQL view, but it can be done in query design/grid
view as well. You just put the subquery in the "WHERE" box under the
PrimaryKey field:

SELECT T.PrimaryKey
FROM MainTable AS T
INNER JOIN TTableWithDupRecords AS W
WHERE T.LinkingFieldName =
W.LinkingFieldName

 

[Guest]

Probably easier to do in SQL view, but it can be done in query design/grid
view as well. You just put the subquery in the "WHERE" box under the
PrimaryKey field:

SELECT T.PrimaryKey
FROM MainTable AS T
INNER JOIN TTableWithDupRecords AS W
WHERE T.LinkingFieldName =
W.LinkingFieldName


--

Ken Snell
<MS ACCESS MVP>





Thanks that should help. Actually it seems like a fairy common request to dlete duplicate firstnames and lastnames in a databse and a cumbersome solution. The main reason to determine there are duplicates in my case is to delete them. I appreciate the hep!

Tom

 

[Ken Snell \(MVP\)]

The "glitch" is that Jet needs to be able to identify the unique record that
is to be deleted, and when you have joins in the delete query Jet sometimes
cannot identify that. This sometimes is helped by putting DISTINCT predicate
in the query, but I usually just use subqueries because then I know that the
query tells Jet exactly what I want it to do and leaves no room for
"interpretation" < g >.

 

[Guest]

Thanks for the explanation about jet. It helps me put it in perspective I
appreciate the assistance.

Tom

 

[giorgio rancati]

Hi,
try this
----
DELETE *
FROM Table
WHERE ID NOT IN
(SELECT MIN(ID)
FROM Table AS T2
WHERE T2.Name=Table.Name
AND T2.Address=Table.Address)

 

[giorgio rancati]

ops...
I forgot the square parenthesis

----
DELETE *
FROM

WHERE ID NOT IN
(SELECT MIN(ID)
FROM

AS T2
WHERE T2.Name=Table.Name
AND T2.Address=Table.Address)
----

bye
--
Giorgio Rancati
[Office Access MVP]

Hi,
try this
----
DELETE *
FROM Table
WHERE ID NOT IN
(SELECT MIN(ID)
FROM Table AS T2
WHERE T2.Name=Table.Name
AND T2.Address=Table.Address)
----

ID is the primarykey autonumber field

bye
--
Giorgio Rancati
[Office Access MVP]

I used a find duplicates query to locate some duplicate records in my
table.
There were 6 duplicate records. I saved the find duplicates query for
later
use. Now I need to create a delete query based on the results of the find
duplicates query to delete the duplicate names and adresses. I opened my
find
duplicates query in design view and changed the query type to a delete
query.
Instead of deleting the duplicate files, it wants to delete every record
in
the table. How can I delete only the duplicate records while keeping the
unique ones?

Thanks for any insights you might have