Unanswered: Combining many to one contact info into Excel-ready flat file
I am transferring data from one database to another using a spreadsheet import tool (Excel) and Access. I have slight familiarity with VBA, and slightly more with SQL (I'm far more comfortable with SQL than with Access's query maker).
My contact information in the original database is odd, but I have collected all of the address, email, and phone information in one database. Since any individual might have more than one address, email or phone, this is a one to many relationship between the AccountNo and the contact information (phone numbers, as one example).
The phone table rows are:
AccountNo, MainContact, Contact, Phone
The AccountNo is linked throughout with the Main Contact (one to one).
The Address, phone, or email is linked to the contact (one to many).
The contact may be the main contact (in which case MainContact=Contact) or it may be a record attached to the main contact (a spouse, friend, lawyer, etc).
For each contact, I would like one row on a Access query or Excel spreadsheet that looks something like this:
AccountNo, MainContact, Contact, Phone1, Phone2, Phone3, Phone4, Phone5
1202x,George, Fred, (773)555-1212, (773)555-1200,(null or placeholder), (null or placeholder), (null or placeholder)
1202x, George, Marc, (608)409-1212, (null or placeholder), (null or placeholder), (null or placeholder), (null or placeholder)
I'll do the same with Address and Email, then join all three tables into one flatfile. I am comfortable with the join, but I am not certain how to turn the existing phone table into the new, phone table.
Thank you very much Paul. This is producing concatenated numbers, but it is doing so very slowly. Limiting my set to just 11 records, and only concatenating the phone numbers, it is taking about a minute or more to update. I have many thousands of records, and three sets of information I need to concatenate (email, phone, address) so this isn't going to work well. Am I stuck?