Hi Al,

I solved this problem myself last night using procedural code. Here is my code:

*SELECT DISTINCT Type FROM Phones INTO CURSOR curTypes

SELECT cID, Phone FROM Phones WHERE  TYPE IN (SPACE(20),'Home')  INTO CURSOR curHome_Phones
INDEX ON cID TAG cID

SELECT cID, Phone FROM Phones WHERE  TYPE  ='Office' INTO CURSOR curOffice_Phones
INDEX ON cID TAG cID

SELECT cID, Phone FROM Phones WHERE  TYPE  LIKE 'Cellular%' INTO CURSOR curCellular_Phones
INDEX ON cID TAG cID

SELECT cID, Phone, TYPE FROM Phones WHERE  TYPE  LIKE 'E-mail%' INTO CURSOR curEmails
INDEX ON cID TAG cID

SELECT cID, Phone, TYPE FROM Phones ;
	WHERE TYPE NOT LIKE 'E-mail%' AND TYPE NOT LIKE 'Cellular%' AND TYPE NOT IN (SPACE(20),'Home','Office')  ;
	INTO CURSOR curOther
INDEX ON cID TAG cID

*!*	SELECT p.LName, p.FName, Home.Home_Phone, ;
*!*	Office.Office_Phone, Cell.Cellular_Phone, Email.Email, Email.EmailType, ;
*!*	Other.Other, Other.Other_Type FROM People P INNER JOIN Entities En ON P.Midno= En.MidNo ;
*!*	LEFT JOIN curHome_Phones Home ON P.cID = Home.cID ;
*!*	LEFT JOIN curOffice_Phones Office ON P.cID = Office.cID ;
*!*	LEFT JOIN curCellular_Phones Cell ON P.cID = Cell.cID ;
*!*	LEFT JOIN curEmails Email ON P.cID = Email.cID ;
*!*	LEFT JOIN curOther Other ON P.cID = Other.cID ;
*!*	WHERE p.Primary = .t. and En.Descrip = 'ACTIV' GROUP BY ;
*!*	LName, FName, Home_Phone, Office_Phone, Cellular_Phone, EMail, EmailType, Other, Other_Type
SELECT 0
CREATE CURSOR curMemos (Home_Phone M, Office_Phone M, Cellular_Phone M,  EMail M, OTHER M)
APPEND BLANK

SELECT p.LName, p.FName,  p.cID, curMemos.* FROM curMemos, People p INNER JOIN Entities En ON p.Midno= En.Midno ;
	WHERE p.PRIMARY = .T. AND En.DESCRIP = 'ACTIV'  ORDER BY cID INTO CURSOR curResult READWRITE

LOCAL lcHome, lcOffice, lcCell, lcEmail, lcOther
#DEFINE CRLF CHR(13) + CHR(10)
WAIT WINDOW NOWAIT 'Preparing final cursor...'
SCAN
	STORE "" TO lcHome, lcOffice, lcCell, lcEmail, lcOther
	IF SEEK(curResult.cID, 'curHome_Phones')
		SELECT curHome_Phones
		SCAN WHILE cID = curResult.cID
			lcHome = m.lcHome + Phone + CRLF
		ENDSCAN
		lcHome = LEFT(m.lcHome,LEN(m.lcHome)  - 2)
	ENDIF
	IF SEEK(curResult.cID, 'curOffice_Phones')
		SELECT curOffice_Phones
		SCAN WHILE cID = curResult.cID
			lcOffice = m.lcOffice + Phone + CRLF
		ENDSCAN
		lcOffice = LEFT(m.lcOffice,LEN(m.lcOffice)  - 2)
	ENDIF
	IF SEEK(curResult.cID, 'curCellular_Phones')
		SELECT curCellular_Phones
		SCAN WHILE cID = curResult.cID
			lcCell = m.lcCell + Phone + CRLF
		ENDSCAN
		lcCell = LEFT(m.lcCell,LEN(m.lcCell)  - 2)
	ENDIF
	IF SEEK(curResult.cID, 'curEmails')
		SELECT curEmails
		SCAN WHILE cID = curResult.cID
			lcEmail = m.lcEmail + ALLTRIM(Phone) + " " + STRTRAN(TYPE,'E-mail','') + CRLF
		ENDSCAN
		lcEmail = LEFT(m.lcEmail,LEN(m.lcEmail)  - 2)
	ENDIF
	IF SEEK(curResult.cID, 'curOther')
		SELECT curOther
		SCAN WHILE cID = curResult.cID
			lcOther = m.lcOther + ALLTRIM(Phone) + " " + TYPE + CRLF
		ENDSCAN
		lcOther = LEFT(m.lcOther,LEN(m.lcOther)  - 2)
	ENDIF
	REPLACE Home_Phone WITH m.lcHome, Office_Phone WITH m.lcOffice, ;
		Cellular_Phone WITH m.lcCell, EMail WITH m.lcEmail, OTHER WITH m.lcOther IN curResult
ENDSCAN
WAIT CLEAR

And final select

SELECT P.LName, P.FName, P.cID, P.Home_Phone as Phone, P.Home_Phone, P.Office_Phone as Phone_A, P.Office_Phone, P.Cellular_Phone as Phone_B, P.Cellular_Phone, P.Email as Phone_C, P.EMail, P.Other, Addr.PrettyName, Addr.City, Addr.State, Addr.Addres1, Addr.Addres2, Addr.Zip FROM curResult P LEFT JOIN Addrs Addr on P.cID = Addr.cID ORDER BY LName, FName, City, State, Zip, Addres1, Addres2

I figured that I would not be able to solve it using SQL-Select. I wish I would understand this much sooner to not waste so much time and money...

BTW, I'm using separate table memo trick because I don't have VFP9 at home. Since this is for Stonefield I could probably simplify one line here using CAST for memo fields.

>>Hi everybody,
>>
>>I can not figure out how to correctly produce the following report:
>>
>>I have two tables: People (cID, LName, FName) and Phones (cID, Phone, Type).
>>
>>There are several different types of the phones (Home, Office, Cell, E-Mail and few others).
>>
>>I want to display the info as
>>

>>Person Info - group
>>
>>Home   Office     Cell  E-Mail (Type)

>
>If you want all the contact information in one row, you only need one total row per person, so you don't need grouping, it all goes in the Detail band. Your Phones table should probably be renamed to something like Contacts.
>
>One approach is something like this:
>

>SELECT ;
>  LName ;
>  , FName ;
>  , GetContactInfo( cID, "Home" ) AS Contact1 ;
>  , GetContactInfo( cID, "Office" ) AS Contact2 ;
>  , ... ;
>  FROM People ;
>  ...
>
>FUNCTION GetContactInfo( tcID, tcContactType )
>LOCAL ;
>  lcRetVal
>
>SELECT ;
>  Phone ;
>  FROM Phones ;
>  WHERE Phones.cID == tcID ;
>    AND Phones.Type == tcContactType ;
>  ...
>
>IF _TALLY > 0
>  lcRetVal = ...
>
>ELSE
>  lcRetVal = ""
>
>ENDIF
>
>RETURN lcRetVal
>

>One problem with this approach is if you add even one Contact type, you have to add a column to your main SELECT. Then you have to remember to add another Contact column in your report.
>
>Another approach is to lump all the Contact information into a single Memo field:
>

>SELECT ;
>  LName ;
>  , FName ;
>  , CAST( GetContactInfo( cID ) AS M ) AS ContactInfo ;
>  , ... ;
>  FROM People ;
>  ...
>
>FUNCTION GetContactInfo( tcID )
>
>* This function gets all the Contact info for a single Person and formats it in
>* a character string, with CRLFs if desired
>
>RETURN SomeCharString
>

>With this second approach, your report contains just a single field to hold the memo so the report doesn't have to be changed later. You just have to update the GetContactInfo() function if you add types.
>
>You can see why some people just leave the report like this <g> :
>

>Person Info (group)
>  Home
>  Office
>  Cell
>  E-mail
>  ...
>