Challenge using SQL

[scubadiver]

To first explain, we have a server that contains 5.5 million records of
financial accounts (I work for a debt collection agency). Some people may
have records with multiple debts with different banks (or whatever). Some of
these duplicates will contain phone numbers and what I need to do is populate
records with no phone numbers from the matched duplicates.

I have imported 40,000 records into a stand-alone access database to do some
testing. I have created a duplicates query and create a new column that
classifies valid and non valid phone numbers.

But where do I go from there ... any ideas?

David

 

[scubadiver]

The catch is that not all the records have phone numbers.

 

[John Spencer]

FIrst thing that comes to mind is to create a query of all the records that
do not have phone numbers and a query of all the records that do have phone
numbers and then join the queries on the same fields as you used to
determine duplicates. Then you probably would be able to use an update
query to update the phone numbers.

Very roughly the Update query might look like

UPDATE QueryNoPhone as N INNER JOIN QueryPhone as P
ON N.SSN = P.SSN and N.LastName = P.LastName
Set N.PhoneNumber = [P].[PhoneNumber]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[scubadiver]

That is what I worked out. A lot simpler than I thought.

But I didn't think of the update query though.

thanks

FIrst thing that comes to mind is to create a query of all the records that
do not have phone numbers and a query of all the records that do have phone
numbers and then join the queries on the same fields as you used to
determine duplicates. Then you probably would be able to use an update
query to update the phone numbers.

Very roughly the Update query might look like

UPDATE QueryNoPhone as N INNER JOIN QueryPhone as P
ON N.SSN = P.SSN and N.LastName = P.LastName
Set N.PhoneNumber = [P].[PhoneNumber]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

To first explain, we have a server that contains 5.5 million records of
financial accounts (I work for a debt collection agency). Some people may
have records with multiple debts with different banks (or whatever). Some
of
these duplicates will contain phone numbers and what I need to do is
populate
records with no phone numbers from the matched duplicates.

I have imported 40,000 records into a stand-alone access database to do
some
testing. I have created a duplicates query and create a new column that
classifies valid and non valid phone numbers.

But where do I go from there ... any ideas?

David

 

[dan sindo]

To first explain, we have a server that contains 5.5 million records of
financial accounts (I work for a debt collection agency). Some people may
have records with multiple debts with different banks (or whatever). Some of
these duplicates will contain phone numbers and what I need to do is populate
records with no phone numbers from the matched duplicates.

I have imported 40,000 records into a stand-alone access database to do some
testing. I have created a duplicates query and create a new column that
classifies valid and non valid phone numbers.

But where do I go from there ... any ideas?

David

Populate the phone number field on the table that has no phone with the
table that has phone numbers using update query with something like
this(below)
table 1 will update phone numbers on table 2 where there are no phone
numbers. The link (ID) can be first name and lastname or any field that are
in both tables.

UPDATE table_1 INNER JOIN table_2 ON table_1.ID = table_2.ID SET
table_1.phone_no = table_2.phone_no;