Converting a table with rows of info into a table with columns

[Ernie]

Brief background: I have a very large 3rd party dbms
(30+Gb) from which I need to extract selected data using
an odbc link. Among that data is a table which stores
customer contact phone numbers stored as rows. In this way
you might have one customer with 4 phone numbers and
another with only 2. I can segregate these numbers into
separate tables with no problems.

What I want to do is take these 4 tables and combine them
into one table with 4 phone numbers. A simple join won't
work because there may be records in table 2 that are not
in table 1, and records in table 3 that are not in any
others. An append query won't work because then I will get
multiple rows for the same customer again, which is what I
want to avoid.

An update query seems ideal for this but I don't
understand how to set one up. When I tried, I either got
empty fields or I get the error "Operation must use an
updatable query."

Maybe a lookup table could be used? If so, how?

Any ideas are welcome (the simpler the better).

 

[Jeff Boyce]

Ernie

Not sure I completely visualize your situation...

Your 3rd party dbms has multiple "phone number" tables? Or you have taken
multiple phone numbers and created separate tables?

Either way, you now have customers (customerIDs?), and tables with
CustomerID, PhoneNumber as fields. ?And you want a single table with all
the phone numbers?

If so, one approach would be to create a new (empty) table and append rows
from each of your (?4) tables. This would require as many append queries as
you have tables to append from. If you set a Unique Index on the
combination of CustomerID and PhoneNumber, you won't get any duplicates.

Or have I misunderstood your situation...?

 

[Guest]

-----Original Message-----
Ernie

Not sure I completely visualize your situation...

Your 3rd party dbms has multiple "phone number" tables? Or you have taken
multiple phone numbers and created separate tables?

Either way, you now have customers (customerIDs?), and tables with
CustomerID, PhoneNumber as fields. ?And you want a single table with all
the phone numbers?

If so, one approach would be to create a new (empty) table and append rows
from each of your (?4) tables. This would require as many append queries as
you have tables to append from. If you set a Unique Index on the
combination of CustomerID and PhoneNumber, you won't get any duplicates.

Or have I misunderstood your situation...?


--
Good luck

Jeff Boyce
<Access MVP>

.

Thank you for your reply, you are close.

In the main dbms (which is really hard to create
reports), I have the following table:

CustomerID1, ContactID1, Phone number1
CustomerID1, ContactID1, Phone number2
CustomerID1, ContactID2, Phone number1
CustomerID1, ContactID2, Phone number2
CustomerID1, ContactID3, Phone number2
CustomerID1, ContactID4, Phone number1
CustomerID1, ContactID4, Phone number3

And on like that (with other useful information). There
might not be a phone1 for a customer, there might not be a
phone 3 for a customer, etc.

Now I am porting that into an access2k db to use in
tracking sales information on CustomerID. I need to lay
out the access db such that up to 4 phone numbers for each
contactid is in the same record like this:

CustomerID1, ContactID1, phone1, phone2, phone3, phone4
CustomerID2, ContactID2, phone1, phone2, phone3, phone4

As noted earlier, I can get a table containing all the
customers who have a phonenumber1, and a second table
having all the phone2's, and a third table with all the
customers with a phone3, etc.

As you can see, a simple join of these 4 tables won't work
because it will skip records which do not match all of the
tables. I tried using an append query to combine my 4
tables into a new super-table but all I get is either
multiple records for the same customerid or blank fields
for the phone numbers. I have also tried doing an update
query with the results posted previously.

I have considered doing a simple join to get all the phone
numbers that I can and then running a program to find out
which records were skipped and using an append query to
add them back in. That seems like a bit more than I want
to do for this project though.

Let me know if you need more info, and thanks for any
response.

 

[Jeff Boyce]

Ernie

If all you want is something that looks like

CustomerID1, ContactID1, phone1, phone2, phone3, phone4
CustomerID2, ContactID2, phone1, phone2, phone3, phone4

I'd suggest you use a spreadsheet!

The original data structure you have:

CustomerID1, ContactID1, Phone number1
CustomerID1, ContactID1, Phone number2
CustomerID1, ContactID2, Phone number1
CustomerID1, ContactID2, Phone number2
CustomerID1, ContactID3, Phone number2
CustomerID1, ContactID4, Phone number1
CustomerID1, ContactID4, Phone number3

seems much more suited to Access' relational data design.

Why do you feel you need to change it?

 

[Ernie]

I was afraid of that ... it will have to be several
spreadsheets as I have about 200,000 customers to deal
with.

Thank you for your time.

 

[Duane Hookom]

Jeff was basically asking "why" you felt this was necessary. There are
fairly easy methods of un-normalizing your tables but we don't want to send
you down a path that none of us would take without justification.

 

[Ernie]

I am in the process of porting selected records from the
large database into Goldmine for sales lead tracking
(Goldmine is much better at this than the large dbms
currently in use). The Goldmine database has 4 phone
number fields in each contact record and I was trying to
fill them with one pass. As it is I'll have to run several
updates to get all four phone numbers in the right places.
Access is just a convenient middle step of the import (one
that I'm at least slightly familiar with).

 

[Duane Hookom]

You could concatenate all the phone numbers into a comma delimited
expression with the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

 

[Jeff Boyce]

Thanks, Duane.

Jeff