Re: Duplicates Query... Help Wanted

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!)
--
Tom Ellison

"Dale" <banana_pages@xxxxxxxxx> wrote in message
news:OTtKvQJxFHA.2656@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>


.

Relevant Pages

  • Re: Suffering
    ... The easiest way to locate the duplicates is to ... FROM PhoneNumber ... procedure until the query returns 0 rows. ... > Outside of using a computed column or composite index, ...
    (microsoft.public.sqlserver.programming)
  • Re: Duplicates Query... Help Wanted
    ... > Dear Dale: ... > SELECT PhoneNumber, CountAS Repetitions ... I need help writing a query that will search each duplicated ... >> remaining duplicates will be deleted, leaving me with only one pretty ...
    (microsoft.public.access.queries)
  • RE: Suffering
    ... > this query to sniff for duplicates: ... > FROM PhoneNumber pn ... > And an issue came up yesterday that there might be duplicates, ...
    (microsoft.public.sqlserver.programming)
  • RE: Suffering
    ... >> And an issue came up yesterday that there might be duplicates, ... >> FROM PhoneNumber pn ... >> CREATE NONCLUSTERED INDEX PhoneNumberMatchingIX ... >> Outside of using a computed column or composite index, ...
    (microsoft.public.sqlserver.programming)