Delete Duplicates 2

[lars7]

Hi,
I want to delete duplicates from my table. I have set up the find duplicate query which shows the original and the duplicate, how do I get it just to show 1 of the records so that I can make a delete query to get rid of it.

Thanks,

Tom.

 

[Golom]

See PHV's second post in thread701-1145120

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[lars7]

Hi,
Unfortunatly I don't have anything unique in my table. It was an excel sheet that I have been sent by payroll to group some members of staff by a group code, pay point and contact name and a few other fields so that we can send out a letter to staff. The duplicate query found the duplicates by matching the GC, PP, and contacts fields.

 

[Golom]

Then that combination of fields constitutes a unique key?

If so then just extend PHV's example to use 3 field MAX values instead of 1.

Another option is this trick

Select * INTO NewTable
From
(Select * From myTable UNION Select * From myTable)

And "NewTable" will now be without duplicates. You can then delete everything from the current (i.e. myTable) table and append NewTable to it OR simply delete myTable and rename NewTable.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[lars7]

Hi Golom,
I am trying your code for the make table query but I am getting a strange result.I have 25 reords in my contacts table which I have renamed "tblfirstcontact", I copied that and imported it back into the table, for demonstration, and in the query I get 32 records 7 duplicates. I imported the file again to give me 75 records in "tblfirstcontact" and I still get 32 reords in the query.

 

[Golom]

The query is going to eliminate duplicate records where every field in a record duplicates every field in another record. If you are counting records as being duplicates when only the fields GC, PP, and contacts are duplicated then you need to use PHV's approach.

DELETE * 

FROM yourTable

WHERE cStr(GC) & cStr(PP) & cStr(Contacts) NOT IN

  (SELECT cStr(Max(GC)) & cStr(MAX(PP)) & cStr(MAX(Contacts)) 
   FROM yourTable 
   GROUP BY [COLOR=blue]... all the other fields in the table ...[/color])

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[lars7]

Hi Again,
This time I have 42 records from the table of 75 (three times 25) this is the code I used and a sample of the data from the criteria fields:

SELECT *
FROM tblfirstcontact
WHERE (((CStr([GC]) & CStr([PP]) & CStr([Contact])) Not In (SELECT cStr(Max(GC)) & cStr(MAX(PP)) & cStr(MAX(Contact))
FROM tblfirstcontact
GROUP BY tblfirstcontact.PD, tblfirstcontact.[Staff Group], tblfirstcontact.[Line 1], tblfirstcontact.[Line 2], tblfirstcontact.[Line 3], tblfirstcontact.Field9));

PD GC PP Staff Group Contact
G56 4B 01 Domestic Jacqui Gallagher
G56 4B 01 Domestic Jacqui Gallagher
G56 4B 01 Domestic Jacqui Gallagher
G56 4C 04 Domestic Sheila Miller
G56 4C 04 Domestic Sheila Miller
G56 4C 04 Domestic Sheila Miller
G56 4E 04 Domestic Sheila Miller
G56 4E 04 Domestic Sheila Miller
G56 4E 04 Domestic Sheila Miller
G56 4F HS Domestic Sheila Miller
G56 4F 04 Domestic Sheila Miller
G56 4F HS Domestic Sheila Miller
G56 4F HS Domestic Sheila Miller
G56 4H DA Domestic Jacqui Gallagher
G56 4H HS Domestic Jacqui Gallagher
G56 4H HS Domestic Jacqui Gallagher
G56 4H DA Domestic Jacqui Gallagher

 

[PHV]

You may try something like this:
SELECT PD,GC,PP,[Staff Group],Contact,Last([Line 1]) AS Line1,Last([Line 2]) AS Line2,Last([Line 3]) AS Line3,Last([Field9]) AS Line4
FROM tblfirstcontact
GROUP BY PD,GC,PP,[Staff Group],Contact

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi There,
I added an autonumber to "tblcontact" and used PHV's code and it worked. I know autonumbers are not recommended but I think it will be ok in this case. Thanks Golom for all your help and advice and PHV for posting the code originally. I am delighted that this has worked but if anyone has the time could they please explain to me why it worked?

DELETE tblfirstcontact.MyId, *
FROM tblfirstcontact
WHERE (((tblfirstcontact.MyId) Not In (SELECT Max(myID) FROM tblfirstcontact
GROUP BY tblfirstcontact.PD, tblfirstcontact.GC, tblfirstcontact.PP, tblfirstcontact.[Staff Group], tblfirstcontact.Contact, tblfirstcontact.[Line 1], tblfirstcontact.[Line 2])));

 

[lars7]

Hi PHV,
Your new code also works but it won't convert in a delete query so I would need another query (the duplicate records reappear). Which code do you recommend I use?

 

[PHV]

My code was a starting point for a make table query, or simply a query to select unique values.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi,
I get it now, I will go with the new code. The file will be imported into "tblfirstcontact" and then the query will make "tblcontacts". Thanks PHV your stars on it's way.