>>

>>SELECT People.cID,;
>>       MAX(LName) AS LName,;
>>       MAX(FName) AS FName,;
>>       MAX(IIF(Phones.Type=='Home'  , Phone, CAST([] AS C(??))) AS Home,;
>>       MAX(IIF(Phones.Type=='Office', Phone, CAST([] AS C(??))) AS Office,;
>>       MAX(IIF(Phones.Type=='Cell'  , Phone, CAST([] AS C(??))) AS Cell,;
>>....
>>FROM People;
>>INNER JOIN Phones ON People.cId == Phones.cId;
>>GROUP BY People.cID
>>

>>
>>That wold give you only one contact per person, but I read Naomi's quest to have one person with seveveral contact possibilities. Adding phones under a different alias in 1 join for each contact type should resolve at least that, but if a person has 2 cell phones, this info would still be lost.
>>
>>First coffee is still being created, so I might be wrong.
>>
>>regards
>>
>>thomas
>
>You're right about the nature of the problem, Thomas. And believe me, I tried so many various combinations trying to solve this by Select-SQL that my brain hurt.
>
>My final conclusion - that's the kind of the problem is hard to solve with select-SQL (unless you want to use UDF or some sort of LOOKUP functions in SQL).
>
>So I just solved the problem by procedural way.
>
>Thanks again.

Hi Naomi, relax your brain ...
try:

SELECT People.cID,;
       People.LName,;
       People.FName,;
       Home.Phone   AS Home,;
       Office.Phone AS Office,;
       Cell.Phone   AS Cell,;
....
FROM People;
LEFT JOIN Phones Home ON People.cId == Home.cId AND Home.Type =='Home';
LEFT JOIN Phones Office ON People.cId == Home.cId AND Office.Type =='Office';
LEFT JOIN Phones Cell ON People.cId == Home.cId AND Cell.Type =='Cell'  ;
....