Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: Multiple Phones One Line

    I have a table that keeps track of a local volunteer group.

    I have the base info(name, address) in one table.

    FriendID
    FirstName
    LastName

    In a second table contains all contact information, 1 per line.

    ContactID
    FriendID
    Contact Type
    Email_Address
    Area Code
    Phone Number
    Preferred

    I'm wondering how to get multiple email addresses or phone numbers on one line.

    FriendID, Phone1, Phone2, Phone3, etc.

    Thanks.

    -Nate

  2. #2
    Join Date
    Mar 2012
    Posts
    3

    Cob Job Answer

    I figured out a round about way of doing it using 3 queries.

    Query 1 gets the count of phones.

    Query 2 gets the max and min of any user with 2 phones.

    Query 3... in column one iif a user has 1 phone list it, else print the max. in column 2 print the min.

    Slick.

    I can see doing it for 3(search for all email addresses not in min or max) but scaling that would be impossible.

    Perhaps someone here can improve on my solution.

  3. #3
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    Rather than have a dedicated field for each phone, email, etc I would have your second table consist of a lookup list called maybe ContactMethod (You've used ContactType for something already!) where you can actually select phone1, phone2, phone3, email1, email2, email3 from a list (and so you could add further methods of contact), ContactDetail which you add alongside and your FriendID. Set your primary key on FriendID and ContactMethod so you can have one of each type of ContactMethod as your lookup allows. Do a query on each of those lookup values that you want to report on before bringing all those queries into one using outer joins to have everything on a single row.
    Last edited by jonesyfella; 03-01-12 at 20:55.

  4. #4
    Join Date
    Mar 2012
    Posts
    3

    That's What I Did

    Thanks JonesyFella,

    I took your advice. Combining the table into one usable output took 5 queries for 2 emails and 1 phone number, but it's done. The 5 queries are:

    1)1 to get all email addresses.
    2)1 to get only users with 2 email addresses
    3)1 to combine the first 2
    4)1 to get all the phone numbers.
    5)1 to combine 3 and 4

    Sigh....

    This is a headache.

    If anyone thinks of a better way, let me know. For now, I'll just limit users to 2 phone and 2 email.

Posting Permissions

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