Delete Similar Records

[jedgretzky]

I am having trouble creating a query that will delete duplicate records. It seems to me that this isn't that hard of a query, but I just can't seem to get it working. What I have is a table that has an ID, which should be unique. Unfortunately there have been entries for the same ID where the Total Value row has been increased. What I want to do is delete the records that have the same ID number but the lower total.

Here is one of the sql queries I wrote to try and do this that didn't work...

delete *
FROM x, x AS x_1
where x.id = x_1.id
and x.Total Value > x_1.Total Value
Or x.Total Vale<x_1.Total Value

Any ideas are appreciated

[FishVal]

Hi, there.

Recordset provided by table self-joining is not updatable, thus no records could be deleted. You should use subquery instead. Run Access wizard for "Find duplicates" query and see how it may be implemented.

Regards,
Fish

[jedgretzky]

After running the subquery to come up with the duplicated data fields, It comes up with all of the rows with duplicate IDs. However I am not sure how to add in the argument to delete certain rows only where the total amount is the largest. Here is what the SQL looked like from this query to find the duplicates.

select x.ID, x.[TOTAL VALUE], x.SORTNAME, x.[DECEASED FLAG], x.NAME, x.[FACE VALUE], x.[TOTAL VALUE]
FROM x
WHERE (((x.ID) In (SELECT [ID] FROM [x] As Tmp GROUP BY [ID] HAVING Count(*)>1 )))

[FishVal]

Something like.

[code=sql]
DELETE *
FROM x
WHERE (((x.ID) In (SELECT [ID] FROM [x] As Tmp GROUP BY [ID] HAVING Count(*)>1 )))
[/code]

This will however delete both duplicates. Guess that is not what you really want.
So the question is how a record to be deleted would be recognized?

Regards,
Fish

[jedgretzky]

The record would have to be identified by the Total_Value field. In the duplicates there will be 2 different values, one Total_Value will be smaller than the other Total_Value field. I want to delete the record that has the smaller Total_Value field.

Here is one idea that I tried but could not get working: This shows basically how I wanted to delete the record where the value is smaller.

delete *
from x T1, x T2
where T1.id = T2.id
and T1.Total Value > T2.Total Value

It almost seems to me that I might need to create an identical table of the data and basically compare the two tables to delete the correct fields.

-Jedd

Something like.

[code=sql]
DELETE *
FROM x
WHERE (((x.ID) In (SELECT [ID] FROM [x] As Tmp GROUP BY [ID] HAVING Count(*)>1 )))
[/code]

This will however delete both duplicates. Guess that is not what you really want.
So the question is how a record to be deleted would be recognized?

Regards,
Fish

[jedgretzky]

I have a select query that selects the records that I want. I think I may just have to enter a delete statement inplace of the select. Unfortunately I will be unable to test this today, it is quiting time.

This is what the select statement looks like:

SELECT x.ID, x.[TOTAL VALUE]
FROM x INNER JOIN x AS x_1 ON x.ID = x_1.ID
WHERE x.[Total Value]< x_1.[Total Value];


Thank you for your assistance with my questions, it definately helped.

-Jedd

[FishVal]

Hi, Jedd.

You may design a query to return records to be deleted.
Something like:
[code=sql]
SELECT Min([Total value]) FROM x GROUP BY [ID] HAVING Count(*) > 1;
[/code]
Then use it in delete query as criteria using IN (subquery) syntax in WHERE clause to check whether a record belongs to to-be-deleted recordset.

Regards,
Fish.

[jedgretzky]

I just tested the query and it worked perfectly, thanks for your help I really appreciate it.

-Jedd

Hi, Jedd.

You may design a query to return records to be deleted.
Something like:
[code=sql]
SELECT Min([Total value]) FROM x GROUP BY [ID] HAVING Count(*) > 1;
[/code]
Then use it in delete query as criteria using IN (subquery) syntax in WHERE clause to check whether a record belongs to to-be-deleted recordset.

Regards,
Fish.