How to check for duplicates in Table?

[SinCity]

I have a Table called TableL. It has a colum called PhoneNumber.
How can I make code so that if a duplicate PhoneNumber is found then
one of the records is deleted? Should I use a Macro? SQL Code?
VB Code?

 

[SinCity]

Rather than trying to get fancy by using a SQL or VBA solution to delete

duplicate records, may I suggest that you use a SQL solution (Find
Duplicates
Query Wizard) to identify the duplicates, and then use human thought
processes to decide which duplicate record(s) to delete based on the
results
returned. Otherwise, you could very easily delete the most current address
info. for the same person, leaving a single record with out-of-date
contact
information.

Hey Tom,

Great idea. I tried that process and it turns out that I would have to
manually delete
1000+ records each time this process is run (which is often). So is there a
way to
have the code delete one of the records?

Here is the closest I have come. The following code will delete both
duplicates,
but I need for it to leave one of them and not delete both of them...

DELETE Leads.HomePhone, Leads.BLastName, Leads.BFirstName
FROM Leads
WHERE ((([Leads].[HomePhone]) In (SELECT [HomePhone] FROM [Leads] As Tmp
GROUP BY [HomePhone] HAVING Count(*)>1 )));

Any other suggestions?

 

[SinCity]

What process are you describing that is creating all of these duplicates

in
the first place? Before we attempt to automate the deletion of anything,
is
the data in each field, for each set of duplicate records, exactly the
same?
If so, an easy approach to use would be to create a Group By query. Then
use
this as a source of data for a Make Table query.

Hey Tom,

Great question! Here is a sample of the process that takes place which
should
answer your question...

First I have a table containing 1000 records
Someone gives me another table of 1000 records which will probably contain
duplicates. It might only contain 2 duplicates or it might contain 800 or
more. It is different each time.
My ultimate goal is to get them both in a single table without there being
duplicates (we can use the Email field as a duplicate reference).

Does that answer your question?

 

[Guest]

Does that answer your question?

Partially. It tells me how you are getting the duplicates. But, I still
don't know if the *entire* record (all fields considered) is a duplicate. If
the data is different for some field(s) in the records with the same e-mail
address, then you've got a different problem versus simply having identical
records throughout.

Oh, and let me revise my past answer. Rather than a Make Table query, it
might be better to use a Group By query as the source of data for an Append
query. That way, your table definition can remain exactly as you like. For
example, a standard Make Table query does not allow one to create a primary
key. However, you could run an append query to append records from one to
another.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[SinCity]

Tom thanks I found an answer to my problem...
http://support.microsoft.com/kb/209183