If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Converting a table with rows of info into a table with columns
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). |
#2
|
|||
|
|||
Converting a table with rows of info into a table with columns
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 |
#3
|
|||
|
|||
Converting a table with rows of info into a table with columns
-----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. |
#4
|
|||
|
|||
Converting a table with rows of info into a table with columns
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? -- Good luck Jeff Boyce Access MVP |
#5
|
|||
|
|||
Converting a table with rows of info into a table with columns
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. -----Original Message----- 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? -- Good luck Jeff Boyce Access MVP . |
#6
|
|||
|
|||
Converting a table with rows of info into a table with columns
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. -- Duane Hookom MS Access MVP -- "Ernie" wrote in message ... 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. -----Original Message----- 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? -- Good luck Jeff Boyce Access MVP . |
#7
|
|||
|
|||
Converting a table with rows of info into a table with columns
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). -----Original Message----- 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. -- Duane Hookom MS Access MVP -- "Ernie" wrote in message ... 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. -----Original Message----- 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? -- Good luck Jeff Boyce Access MVP . . |
#8
|
|||
|
|||
Converting a table with rows of info into a table with columns
You could concatenate all the phone numbers into a comma delimited
expression with the generic concatenate function found at http://www.rogersaccesslibrary.com/O...p#Hookom,Duane -- Duane Hookom MS Access MVP "Ernie" wrote in message ... 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). -----Original Message----- 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. -- Duane Hookom MS Access MVP -- "Ernie" wrote in message ... 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. -----Original Message----- 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? -- Good luck Jeff Boyce Access MVP . . |
#9
|
|||
|
|||
Converting a table with rows of info into a table with columns
Thanks, Duane.
Jeff |
Thread Tools | |
Display Modes | |
|
|