Custom delete & duplicate query

[ramez75]

Hi,

I been trying to come up with ideas but so far non is giving me the outpu I want.

I am trying to create a query to find duplicates and delete the duplicates. The result will eventually be used in another query (append query) to update a table.

So let me shed more light on what I am running into. I have a table with 4 columns lets say for simplicity they are A, B, C, D

I want my query to find duplicates within B and deleting them. The catch is before deleting them I need to look into column A to ebsure they are actually duplicates. Example below

Example
A B
John Doe Tires
John Doe Wipers
Allison Doe Tires
Allison Doe Tires

As you can see from the above Tires is a duplicate and need to be deleted only for Allison and not John so my table need to look like that

A B
John Doe Tires
John Doe Wipers
Allison Doe Tires


So any ideas how to get going around this

Thanks in advance

 

[sanzoo]

Maybe the easiest would be to put this table into query and then set unique values within property sheet to "YES" and then you may create new table from it and delete the old one ?

Cheers
sanzoo

 

[ramez75]

Maybe the easiest would be to put this table into query and then set unique values within property sheet to "YES" and then you may create new table from it and delete the old one ?

Cheers
sanzoo

Sanzoo, I dont follow what you are saying. currently the result that I shared in the example is from a query. I need to know what to do to the query so I can get something like this

A B
John Doe Tires
John Doe Wipers
Allison Doe Tires

 

[sanzoo]

Ok. In query design view once you selected all the fields you want you go and click on property sheet from menu which will then appear on the right side and then within general properties you change Unique Values to YES.I hope this will help.

Cheers

 

[ramez75]

Oh I see, I already tried that and I still get the same result

 

[ramez75]

I gave up on the query thing as I cant get the results I want so another idea I had was using vba to go into the table with duplicates and compare the fields in columns A and B from one row ro the other and delete the duplicate row and keep going till end of table.

Any wrote a vba to do that. I am doing a search and see if I can come up with something.

I feel this will be a cleaner approach

 

[JHB]

If you table has an ID number, then you can use a subquery:
Here is a solution from http://allenbrowne.com/subquery-01.html

Delete duplicate records

This example uses a subquery to de-duplicate a table. "Duplicate" is defined as records that have the same values in Surname and FirstName. We keep the one that has the lowest primary key value (field ID.)
DELETE FROM Table1
WHERE ID <> (SELECT Min(ID) AS MinOfID FROM Table1 AS Dupe
WHERE (Dupe.Surname = Table1.Surname)
AND (Dupe.FirstName = Table1.FirstName));

 

[sanzoo]

Maybe you could post a sample of your initial table with same dummy data ?