Duplicate Query

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

I have a table with a lot of duplicate entries. When I
use the Find Duplicates query wizard, the resulting SQL
displayes both occurances of a duplicate record. This of
course doesn't help me when trying to remove the
duplicates. Can someone tell me how to modify the
following SQL string so it only displays one occurance of
the record so I can use it as a delete from statement?
Thanks.

SELECT [Lead_Date], [Phone_Number], [List_Code],
[Country], [Area_Code], [First_Name], [Last_Name],
[Street1], [City], [State], [Zip], [Project], [Agt_Code],
[Agent_Name], [Agent_Phone], [Agent_Fax], [Branch_Code]
FROM [QL Table]
WHERE [Lead_Date] In (SELECT [Lead_Date] FROM [QL Table]
As Tmp GROUP BY [Lead_Date],[Phone_Number] HAVING Count(*)
1 And [Phone_Number] = [QL Table].[Phone_Number])
Click to expand...
ORDER BY [Lead_Date], [Phone_Number];
 
It really isn't that easy. Depending on your situation, you may end up with
the dreaded, "Cannot delete from specified tables" error.

Try this:
Use your query as the basis for a new Make Table query. Utilize the Group
By operator, on the unique value, to assist in pulling the unique records
and add them to a new table.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Terry said:
I have a table with a lot of duplicate entries. When I
use the Find Duplicates query wizard, the resulting SQL
displayes both occurances of a duplicate record. This of
course doesn't help me when trying to remove the
duplicates. Can someone tell me how to modify the
following SQL string so it only displays one occurance of
the record so I can use it as a delete from statement?
Thanks.

SELECT [Lead_Date], [Phone_Number], [List_Code],
[Country], [Area_Code], [First_Name], [Last_Name],
[Street1], [City], [State], [Zip], [Project], [Agt_Code],
[Agent_Name], [Agent_Phone], [Agent_Fax], [Branch_Code]
FROM [QL Table]
WHERE [Lead_Date] In (SELECT [Lead_Date] FROM [QL Table]
As Tmp GROUP BY [Lead_Date],[Phone_Number] HAVING Count(*)
1 And [Phone_Number] = [QL Table].[Phone_Number])
Click to expand...
ORDER BY [Lead_Date], [Phone_Number];
Click to expand...
 
Back
Top