Crosstab Query Sum by Group

[JCtheEUC]

I have a crosstab query that totals data by month. I also need it to sum that data for each employee.

example: (this is what I have currently)
Employee--jan--feb--mar--total
Jack-------2----3----4----9
Jill---------1----2----5----8
Hill---------5----2----4----11

I need...
Employee--jan--feb--mar--total
Jack-------2----3----4----9
Jill---------1----2----5----8
Hill---------5----2----4---11
---------------------Total 28

This info will be displayed to the user on a report.
I'm not sure if I need to change the query or if I can put it into the report.

Hopefully someone out there can help me.

Thanks!

 

[SydneyGeek]

It's easiest to do this in the report, if you are printing one anyway.

1. Create a report using the Wizard
2. In Design view, display the report footer if it's not already there.
3. Add a text box for each field that you want to total. I usually cheat by copying all the fields from the Detail section a a row, and pasting into the report footer. Line them up so they look OK.
4. Right, now to turn them into totals. Go to each textbox in turn. Let's say the textbox's control source is MyField. Change that in the report footer version to =SUM(NZ([MyField],0))

The NZ ensures that all fields will have something in them; Access will not display a total if it encounters a null value

Denis