Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009

    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

    1202x, George, Fred, (773)555-1212
    1202x, George, Fred, (773)555-1200
    1202x, George, Marc, (608)469-1212

    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.

    Any help would be greatly appreciated.
    Last edited by zetetic12; 12-14-09 at 17:07.

  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6

  3. #3
    Join Date
    Dec 2009
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts