>>>Try
>>>

>>>SELECT count( DISTINCT person.PersonID) as PersonCount , Sum(pinfo.workhour) as TotalHours ;
>>>	FROM Person INNER JOIN Pinfo ON person.personid = pinfo.personid ;
>>>	WHERE     (Person.startdate >= {^2012-05-01};   
>>>                             Person.startdate <= {^2012-05-31};
>>>                            AND upper(Person.court) = "R";
>>>                            AND upper(Person.sex) = 'M') ;
>>>                           OR (Person.startdate >= {^2012-05-01};
>>>                           AND Person.startdate <= {^2012-05-31};
>>>                           AND upper(Person.court) = "J") ;
>>>                          AND upper(Person.sex) = 'M' )
>>>

>>>I got rid of conditions by the PersonID completely.
>>
>>
>>
>>thanks a milion Naomi. It is exactly what I was after.
>>
>>Nick :)
>
>You're welcome.


Naomi, I have one final quirk that I need to add to the sum field in the select statement, but it driving me nutty for couple of day. The sum(pinfo.workhour) as TotalHours needs to be filtered further down to containing only workdates between a range of dates. I've tried" for" statement and also "where" clause, but I get errors because I'm not formulating the query right. Here is what I did:

Select Count( Distinct person.PersonID) As PersonCount , Sum(pinfo.workhour) As TotalHours;
   for (pinfo.workdate >= {^2012-05-01} and pinfo.workdate <= {^2012-05-31};
		FROM person INNER Join pinfo On person.PersonID = pinfo.PersonID for  ;
		WHERE (person.startdate >= {^2012-05-01};
		AND person.startdate <= {^2012-05-31};
		AND Upper(person.court) = "R";
		AND Upper(person.sex) = 'M') ;
		or (person.startdate >= {^2012-05-01};
		AND person.startdate <= {^2012-05-31};
		AND Upper(person.court) = "J" ;
		AND Upper(person.sex) = 'M' );

do I need to put pinfo.workdate greater than ,less than filtering in the where clause or can it be a filter where the sum field clasue is? I need to retrieved all the sum total pinfo.workhours that have pinfo.workdate equal to same date ranges used for person.startdate.

thanks
nick