Crosstab Query & Value Criteria

[smaurice]

I have a database that employees use to log billable hours toward
projects.

I have a Crosstab Query that uses a parameter to find a project numbe
and then displays the number of hours that each employee worked on
project during weeks as well as what their billing rate for the projec
was.

What I need to do is filter out values that are below a certain leve
i.e. I don't want to see if Jane worked 1 hour in fiscal week 40 an
Dick worked 30 min in week 39. Basically, in my field that i
Sum(Billable Hours) I'd like to add a where clause somehow that woul
be that Sum(Billable Hours) > 4.

I have a form named "051205 - Hourly Summary" that retrieves m
parameters and I get all my data from a Query named "SubQuery
Crosstab HrSum".

I've pasted my SQL if that helps at all. ---------


PARAMETERS [Forms]![051205 - Hourly Summary]![Combo5] Text ( 255 );
TRANSFORM Sum([SubQuery - Crosstab HrSum].BillableHours) A
SumOfBillableHours
SELECT [SubQuery - Crosstab HrSum].Week, [SubQuery - Crossta
HrSum].HourlyRate
FROM [SubQuery - Crosstab HrSum]
WHERE ((([SubQuery - Crosstab HrSum].[Project #])=Forms![051205
Hourly Summary]!Combo5))
GROUP BY [SubQuery - Crosstab HrSum].SortCode, [SubQuery - Crossta
HrSum].Week, [SubQuery - Crosstab HrSum].HourlyRate, [SubQuery
Crosstab HrSum].[Project #]
ORDER BY [SubQuery - Crosstab HrSum].SortCode
PIVOT [SubQuery - Crosstab HrSum].[Employee Name]

 

[[MVP] S.Clark]

I believe this comes back to the WHERE vs. HAVING debate. WHERE exlcude
data before processing, and HAVING excludes after.

Here's a google hit that explains and provides other examples that might
help.
http://groups.google.ae/group/micro...ERE+vs.+HAVING&rnum=10&hl=en#66bdffffc1ab27a5

So, I would think that you would want to use HAVING to exclude those
records with a value greater than 4.