I've created an SQL statement for which I'm also trying to get the number of people (without duplicates) who are attending a conference. I initially tried to do this in the report summary (creating a field based on first+middle+last, but that didn't give me the correct results. I then tried the following, figuring that my order header table will contain one record for each person who is attending a conference, so I could just count those (the line below where I use "...as TEMP"), but that didn't work either.

The SQL without that "as temp" line creates a cursor which has duplicate names because those people are attending multiple events. I need to know that info, but also need to have just a staight count for my report.

SELE NVL(Cfevents.ev_eventdesc,"") AS EVENTS, NVL(Cforderl.ol_quantity,0) AS QTY, ;
NVL(Cfpeople.pe_firstna,"") AS FIRST, ;
NVL(Cfpeople.pe_middlei,"") AS MIDDLE, NVL(Cfpeople.pe_lastnam,"") AS LAST, ;
NVL(Cfjobtyp.jo_jobdesc,"") AS JOB, NVL(Cfdistri.di_name,"") AS DISTRICT, ;
COUNT(Cforderh.oh_orderno) AS temp ;
FROM Confdata!cfevents ;
	LEFT JOIN confdata!cforderl ;
	  	ON Cforderl.ol_evcode = Cfevents.ev_code ;
	LEFT JOIN confdata!cforderh ;
		ON oh_orderno = ol_orderno ;
	LEFT JOIN Confdata!Cfpeople ;
		on pe_peoplid = oh_peoplid ;
    LEFT JOIN confdata!cfjobtyp ;
    ON Cfpeople.pe_jobid = Cfjobtyp.jo_jobid ;
    LEFT JOIN confdata!cfdistri ;
    ON Cfpeople.pe_distcode = Cfdistri.di_code ;
   WHERE SUBSTR(Cfevents.ev_code,1,4) = ALLT(STR(YEAR(DATE()))) ;
   ORDER BY ev_eventdesc, jo_jobdesc, pe_lastnam ;
   INTO CURSOR Rptdata

<\PRE>

Thanks!

Sylvia