Crosstab Query Sum Down? How?

[Guest]

I have a crosstab query that looks kinda like this.

Store # is the row heading.
Sales goal is the heading.
Sum of sales is the data.

Aggressive Balanced Conservative Total
123 1,000 500 100 1,600
124 100 200 200 500
125 300 300 400 1,000

I'd like for the query, on the same table, to add up all the aggressive,
balanced, and total figures. Under aggressive I'd like it to read 1,400.
Under balanced Id like it to read 1,000. Under conservative I'd like it to
read 700. Under total Id like it to read 3,100.

Any ideas?

 

[Guest]

You won't be able to get a total to show up in the query, unless you use a
temporary table to store the results. You'd likely need to use VBA code to
write the various summed values to a temporary table.

Another alternative is to use a pivot table, instead of a crosstab query, if
your version of Access supports pivot tables (introduced in Access 2002).

If your crosstab query columns will be fixed (ie. Aggressive, Balanced,
Conservative and Total), you can use your crosstab query as a source of data
for a report, and use textboxes in a group footer of a report to show the
sums for these fields. Set the control source for these textboxes =
Sum([FieldName]), for example:

=Sum([Aggressive]) =Sum([Balanced]) =Sum([Conservative]) =Sum([Total])


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Duane Hookom]

Or create a form or report from your crosstab with totals in the footer.
Another solution would be to create another crosstab with a Row Heading of a
constant like "TOTALS". This should fresult in a single row output. Use a
union query to add this row to the end of your existing crosstab.

--
Duane Hookom
MS Access MVP
--

You won't be able to get a total to show up in the query, unless you use a
temporary table to store the results. You'd likely need to use VBA code to
write the various summed values to a temporary table.

Another alternative is to use a pivot table, instead of a crosstab query,
if
your version of Access supports pivot tables (introduced in Access 2002).

If your crosstab query columns will be fixed (ie. Aggressive, Balanced,
Conservative and Total), you can use your crosstab query as a source of
data
for a report, and use textboxes in a group footer of a report to show the
sums for these fields. Set the control source for these textboxes =
Sum([FieldName]), for example:

=Sum([Aggressive]) =Sum([Balanced]) =Sum([Conservative])
=Sum([Total])


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I have a crosstab query that looks kinda like this.

Store # is the row heading.
Sales goal is the heading.
Sum of sales is the data.

Aggressive Balanced Conservative Total
123 1,000 500 100
1,600
124 100 200 200
500
125 300 300 400
1,000

I'd like for the query, on the same table, to add up all the aggressive,
balanced, and total figures. Under aggressive I'd like it to read 1,400.
Under balanced Id like it to read 1,000. Under conservative I'd like it
to
read 700. Under total Id like it to read 3,100.

Any ideas?