Sum ... Avg ... StDev ... in one report with crosstab query

[gwoman]

I have a report based on a crosstab query created from multiple subqueries ...
The user wants to see ... the count of each response per question ... within a specific subject area ... within a specific module ... group with totals.
I have done this ...
What I can't figure out is this ...
They also want to see an average and Standard Deviation on the same report broken down the same way ... but based on each responses rating value ... my crosstab won't let me have more than one value ... help!
Does anyone have any suggestions ... I would like to try to stay away from a subreport unless it is the last option ... THANKS!!!

gwoman

 

[Knicks]

Yes, I"ve done some stuff like this before.

Create another query for your averages, STdv, ect.. and make sure you have the necessary fields for linking later to your crosstab.

Then add this new query to your crosstab and link it, I usually use option 2 for the linking type, but judging by the info given so far you'll probably be fine with the default link. Then you can add those fields from the new query to your crosstab query

 

[Knicks]

Sorry to clarify,

Make new query with STdv, average, ect.. (with linking field)

Then create a new query with above query and crosstab query and link

Put all your fields from crosstab into this new query, and add the stdv, avg fields also....

 

[gwoman]

Thanks ... I'll give it a try ... =)

 

[dhookom]

If you want to create a crosstab with multiple values, check out faq701-4524.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gwoman]

Thanks ... got it to work ... on the first grouping ... the total responses per survey question ... but my total responses per subject area (which consists of all the responses from the questions that fall in a specific subject area)and Module Totals (which consist of all the responses from the questions that fall in a specific subject area that is within a specific module) the AVG and STDEV totals that are in the subject area footer and the module footer are not correct ... doesn't Access automatically carry the total calculations into the footers in association to the headers???
Thanks!
gwoman