Delete dups from a qry

[matthewR]

I have a qry that I want to remove the dups from. I have this code but where do I put it and is it possible or do I have to do this for a table which I can't. Or can I use the query where I found the dups to remove them from the query. I want to remove just 1 and leave 1.

SELECT [Client Number], Count(1) as DuplicateCount
From Lrqry_03C_1
GROUP BY [Client Number]
HAVING Count(1) > 1;

[HansV]

The SQL that you posted is for a query that shows you which records are duplicate on Client Number.
If you want a query that returns each unique Client Number just once, use

SELECT DISTINCT [Client Number]
FROM Lrqry_03C_1

If you want to remove duplicate records from the underlying table, you will have to come up with a way to decide which of the two (or more) you want to delete. Is there another field that distinguishes the records with the same Client Number and that we could use for this purpose?

[matthewR]

This has to be done at the query level. I thought maybe I could do a running count. The query is a totals query. Can I do an expression like:

runcnt: Dcount("*","Lrqry_03C_2","[Client Number] = " & Chr(34) & [Client Number] & Chr(34) &)

Not sure of the sintax.

[HansV]

I'm afraid you've lost me. Could you try to explain in words what you want the end result to be?

[matthewR]

Right now in the query, I have 4609 records due to 10 client numbers that have duplicates. I thought that if I could do a running count on client number, I could say running count not = 2.

I used this
runcnt: DCount("*","Lrqry_03C_2","tblCBA.[Client Number] = " & Chr(34) & [tblCBA].[Client Number] & Chr(34))

in a totals query as an expression and the column has a #Error.

[HansV]

Do you have a field in Lrqry_03C_2 that has different values for records with the same Client Number (for example a primary key field)? If so, what is its name?

[matthewR]

No, Client Number is the key.

[HansV]

If you have two records with the same Client Number, do all other fields have the same value too? Or can there be fields that have different values despite Client Number being the same?

[matthewR]

The duplicates I want to delete have a blank for county name and the one I want to keep has a county name. Everything else would be the same. I did sort on client number and county name.

[HansV]

If you want to select only records that have a county name:

SELECT *
FROM Lrqry_03C_2
WHERE [County Name] Is Not Null

If you want to delete records without a county name:

DELETE *
FROM Lrqry_03C_2
WHERE [County Name] Is Null

Replace County Name with the actual name of the county name field.

[matthewR]

I only want to delete those records without a county name that have 2 client numbers. There are other client numbers that do not have a county name but they do not have two records.

[HansV]

Try this:

Code: Select all

DELETE * 
FROM Lrqry_03C_2 
WHERE [County Name] Is Null AND 
    (SELECT Count(*) 
     FROM Lrqry_03C_2 AS L 
     WHERE L.[Client Number]=Lrqry_03C_2.[Client Number]) > 1

The WHERE clause selects those records that have a blank County Name and where Client Number has duplicates.

[Pat]

Are there any instances where there are no country names for a client?