Duplicate Query

[Humanski]

Hello,
I have recently done a duplicate query on a table. It has reveled that I have 16000 duplicates. Out of the 16000 i want to delete 8000. As 8000 are valid and will be needed.

ID Date Last Date
12 12/12/01 12/12/01
12 12/12/08 13/12/10

The list looks like the one above, the last date field is just an extra, it was not added to the duplicate query. What I want to do
know is delete one of these duplications. eg just delete the second one and not the first. Is this possible? That means my results should be 8000 records and not 16000.

My current query looks like this : -

SELECT DISTINCTROW [07/02VA].Load_Date, [07/02VA].Inv_No, [07/02VA].Ref_No, [07/02VA].ID_No, [07/02VA].QTY, [07/02VA].Wt, [07/02VA].Val_EU, [07/02VA].VX_Date, [07/02VA].Last_Date
FROM [07/02VA]
WHERE ((([07/02VA].Load_Date) In (SELECT [Load_Date] FROM [07/02VA] As Tmp GROUP BY [Load_Date],[Inv_No],[Ref_No],[ID_No],[QTY],[Wt],[Val_EU],[VX_Date] HAVING Count(*)>1 And [Inv_No] = [07/02VA].[Inv_No] And [Ref_No] = [07/02VA].[Ref_No] And [ID_No] = [07/02VA].[ID_No] And [QTY] = [07/02VA].[QTY] And [Wt] = [07/02VA].[Wt] And [Val_EU] = [07/02VA].[Val_EU])))
ORDER BY [07/02VA].Load_Date, [07/02VA].Inv_No, [07/02VA].Ref_No, [07/02VA].ID_No, [07/02VA].QTY, [07/02VA].Wt, [07/02VA].Val_EU, [07/02VA].VX_Date, [07/02VA].Last_Date;

After this has been run I get all of the duplicates from Table 07/02VA, but half of them are useful because a mistake was made behind the scenes so they were accessed twice when they were not allowed. which means the only difference us the last_date.

Thanks Pawel

 

[Pat Hartman]

The easiest way to get rid of duplicates is to create a new table with the appropriate key defined so that duplicates will not be allowed. Then append your data to the new table. The first instance of each set will be added to the table and the second will be discarded. Just click OK when presented with the error message after the append.