>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

You can have two variations and you need to decide which one is correct:

This one:

Select Count( Distinct person.PersonID) As PersonCount , ;
Sum(IIF(pinfo.workdate >= {^2012-05-01} and pinfo.workdate <= {^2012-05-31},pinfo.workhour,0000.00)) As TotalHours;
		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) IN ("R","J") ;
		AND Upper(person.sex) = 'M' 

Will return all people satisfying your where criteria, but only work hours in the workdate ranges.

This query:

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

Will only select people who worked in the work date range.

Now, if you need to exclude the non work days from that query and calculation (assuming someone worked in these days), you need to have a Calendar table that clearly tells which days are work days and which are not. Then you'll join with that Calendar table to get your info.