>
>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.
If it's not broken, fix it until it is.
My Blog