Need help with less complex Query

  • Thread starter Thread starter Patrick Jackman
  • Start date Start date
P

Patrick Jackman

I have a table with ClientPhone numbers. A client may have more than one
phone number.
ClientID
PhoneNumber
....

How can I use SQL to generate a list of ClientID's with all their phone
numbers on one line?

Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Patrick Jackman
Vancouver, BC
604-874-5774
 
I have a table with ClientPhone numbers. A client may have more than one
phone number.
ClientID
PhoneNumber
...

How can I use SQL to generate a list of ClientID's with all their phone
numbers on one line?
Click to expand...

For an arbitrary number of phone numbers, that's really tough: you need a
multiple self-join with as many instances of your table as the maximum number
of phones, using left joins so instances with fewer will not be lost, and
criteria to prevent duplicate phone numbers.

UNTESTED AIR CODE:

SELECT A.ClientID, A.PhoneNumber, B.PhoneNumber, C.PhoneNumber
FROM (yourtable AS A LEFT JOIN yourtable AS B ON B.ClientID = A.ClientID) LEFT
JOIN yourtable AS C ON B.ClientID = B.ClientID
WHERE (B.PhoneNumber > A.PhoneNumber OR B.PhoneNumber IS NULL)
AND (C.PhoneNumber > B.PhoneNumber OR C.PhoneNumber IS NULL OR B.PhoneNumber
IS NULL)

Gets much snarkier with more tables of course.

I'd use VBA, it's a LOT simpler:

http://www.mvps.org/access/modules/mdl0004.htm
 
Thanks John. I'll play with that for a bit before I go ahead and write
another function.

Patrick.

I have a table with ClientPhone numbers. A client may have more than one
phone number.
ClientID
PhoneNumber
...

How can I use SQL to generate a list of ClientID's with all their phone
numbers on one line?
Click to expand...

For an arbitrary number of phone numbers, that's really tough: you need a
multiple self-join with as many instances of your table as the maximum
number
of phones, using left joins so instances with fewer will not be lost, and
criteria to prevent duplicate phone numbers.

UNTESTED AIR CODE:

SELECT A.ClientID, A.PhoneNumber, B.PhoneNumber, C.PhoneNumber
FROM (yourtable AS A LEFT JOIN yourtable AS B ON B.ClientID = A.ClientID)
LEFT
JOIN yourtable AS C ON B.ClientID = B.ClientID
WHERE (B.PhoneNumber > A.PhoneNumber OR B.PhoneNumber IS NULL)
AND (C.PhoneNumber > B.PhoneNumber OR C.PhoneNumber IS NULL OR B.PhoneNumber
IS NULL)

Gets much snarkier with more tables of course.

I'd use VBA, it's a LOT simpler:

http://www.mvps.org/access/modules/mdl0004.htm
 
Thanks John. I'll play with that for a bit before I go ahead and write
another function.
Click to expand...

Well, you could just copy and paste the function from the mvps.org website
link. It returns a concatenated text string, and should work just fine for
your database. Up to you!
 
Back
Top