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

    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.


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

    Contact Type
    Area Code
    Phone Number

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

    FriendID, Phone1, Phone2, Phone3, etc.



  2. #2
    Join Date
    Mar 2012

    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.


    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
    Harrogate, N.Yorks, UK
    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 19:55.

  4. #4
    Join Date
    Mar 2012

    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


    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