Crosstab Query Issue

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

Hello. I'm using Access 2k.

I'd like to know if there is a way to have a query sum the results of
another crosstab query where the field names are dynamic.

For example:

I have a crosstab query based on my employees table, where the names appear
accross the top (as fields) with hours worked as the value. So the query
results look like:

John Jim Mary Tim Phil
3 5 6 2 6


I have a second query that sums up all the hours for all employees (ie, sum
of [John]+[Jim]+[Mary]+[Tim]+[Phil]) = 22 hours

But what if I add another employee (Susan) next month? My second query that
sums all the hours will have to include the field [Susan].

Is there a way to do this dynamically? Is coding this in VBA the answer?
Maybe there is an easier way of looking at this that I'm not seeing.

Thanks for any and all help
 
Your crosstab query wil do it for your.

In design view scroll to right to add a field in the grid like this for the
crosstab example below --
FIELD -- Total Of statuscount: statuscount
TABLE -- geebee
TOTAL -- Sum
CROSSTAB -- Row Heading

TRANSFORM Sum(geebee.statuscount) AS SumOfstatuscount
SELECT geebee.status, Sum(geebee.statuscount) AS [Total Of statuscount]
FROM geebee
GROUP BY geebee.status
PIVOT Format([dated],"Short Date");
 
Back
Top