Duplicates Query... Help Wanted

[Dale]

Hello everyone..

I have a MS Access database (2000) with duplicate phone Numbers and Fax
numbers. I need help writing a query that will search each duplicated
record and compare the records. (I can also use a 2003 Version of MS
Access.

Take the record that has the most populated fields and search the other
duplicates. Find populated fields from the other less populated records and
"move" the data from those less populated records to the empty fields in the
master record.

When I am done, the master record will be as populated as possible, and the
remaining duplicates will be deleted, leaving me with only one pretty
populated record, with unique phone and fax numbers.

Geese, I hope I explained that good enough. I want to run the query on the
fax field and the phone field, one query at a time.

Is anyone up for the challange?

I will send a donation to the one that helps me, via paypal.

Dale

 

[Tom Ellison]

Dear Dale:

The first step would be to find those values of PhoneNumber that recur:

SELECT PhoneNumber, Count(*) AS Repetitions
FROM YourTable
GROUP BY PhoneNumber
HAVING Count(*) > 1

I'll use this later to provide the list of PhoneNumber values, but without
the COUNT(*)

Now, to search your table to show who uses these PhoneNumber values:

SELECT <some fields>, PhoneNumber, (SELECT COUNT(*) FROM YourTable T1 WHERE
T1.PhoneNumber = T.PhoneNumber GROUP BY PhoneNumber) AS Repetitions
FROM YourTable T
WHERE PhoneNumber IN (SELECT PhoneNumber FROM YourTable GROUP BY
PhoneNumber HAVING COUNT(*) > 1)
ORDER BY PhoneNumber

Where this says <some fields> add a list of those fields that identify the
row (probably uniquely) and any other information useful to your purpose.
What you need for FaxNumber would be quite similar.

You must substitute your actual column names and table names into the above.

Please let me know how this works for you. Next, shall we look at how and
why this works (at least I hope it does!)

 

[Dale]

Hello Tom...

You seem to know what you are talking about, however I am such a novice I
have no Idea if this will work. Would you please take this a step further
and go to the contact me area on my website at www.daleallen.com and give me
your email address. I will send you the database structure so you can write
an actual query to do what I want. Then give me your PayPal Email address
and I will send you a donation.

Interested?

Dale

 

[Tom Ellison]

Dear Dale:

My email address is as reported in all my posts. I get a lot of spam as a
result, but I just deal with that.

I replied through the email link in your website as well.

I'll be glad to help in any way you wish.

 

[Dale]

I saw that messasge from my website. I'll get back to you in a few days.

Thanks

Dale