Create a "Totals" row for each column in crosstab

[accessnewbie352]

I am trying to create a Grand Total column in my crosstab query so that it will show the total of each column. Is there a way to do this? I have tried creating a new simple query with the Grand Totals but when I try to link it to the crosstab, it is not displaying.

[June7]

You say Total column but did you mean Total row? How would joining another query to the CROSSTAB show a total for each column?

Options for a Total row are:

1. table, query, or datasheet form - click the Totals button on ribbon to open a Totals row at bottom of query or form

2. aggregate function expression in footer section of form or report

[accessnewbie352]

I need the total for each column in the query though (total of the verticals) not the rows. I currently already have the row totals. Would the aggregate expression allow for the total column?

[June7]

Did you try the suggested options?

[accessnewbie352]

Yes - I tried the first option and it returned the total for each row but I need the total for each column instead. For the second option, I'm not sure if I did it right but I opened a text box and used the =sum expression to pull the total calculation. However, when I go back to the form view, I get an error and no actual calculations.

[Demerit]

Have you verifired your field names?
If one of the SUM function is wrong on the form, the other ones return "error"

[accessnewbie352]

Yes, I have - however the calculated field is pulling off of a different query than the rest of the fields in the form. Is it possible to create a form with fields from two different queries? I am ultimately trying to make the form look like a pivot table with sum total for each row and each column. I have successfully been able to do so using the crosstab query to show sum total for each row but now need to show the total across the bottom for each column.

[Demerit]

Is it possible to create a form with fields from two different queries?

Yes it is possible but for a single form
the queries will form subforms

Are you using subforms?

[accessnewbie352]

No right now I have a single form and was hoping to avoid doing a subform. I will try now creating a subform to pull in the other total and will report back if it works. Thanks for the suggestion.

[June7]

Both of my suggestions were intended to sum each column. Option 1 works, however, unlike a normal SELECT query, the Total row will not stay enabled on a CROSSTAB query once it is saved and closed.

Of course it is possible to include queries in a query. How are the queries related?

If you want to provide db for analysis, follow instructions at bottom of my post.

Building a report to run perpetually based on a CROSSTAB query can be tricky. http://allenbrowne.com/ser-67.html

[accessnewbie352]

Thank you for the link - on the site, the author mentions: "In Access 2007 and later, you can also show the total at the bottom of each column, by depressing the Totals button on the ribbon. The button is on the Records group of the Home tab, and the icon is an upper case sigma (Σ)" This is precisely what I want but I do not see this as a readily available option in my version of the access. In 2010, I do see the sigma option and I have already enabled it to allow for other aggregate calculations. He does not mention further on how I would be able to get the total at the bottom of each column though??

[June7]

With table, query, or datasheet form, click the Totals button which should open a totals row at bottom. Click into the cell below each column and select a function.

[accessnewbie352]

The subform worked but it is now showing the data going down via column form whereas I want it to go across horizontally to mirror the setup of the main form. I posted in another thread under the "form" discussion but is there a way to change the layout of the subform to have the values go across horizontally, rather than going straight down?

I tried the Totals button again as June7 suggested but it was still giving me totals of the row values, but I want the total of the column values.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.