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?

> 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?

> 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?

> 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/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"SinCity" wrote:

> 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?

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