Add New Field to Crosstab Query

[accessnewbie352]

Hello - I am trying to add a new field to a crosstab query but when I pull the object down into the grid and click on "view spreadsheet" mode, it is not appearing.



The crosstab query currently has different departments on top, and different businesses on the side. I am calculating percentages for each department-business in the query and they look to be working fine. However, I would like to add one more column to show overall total percentage by business (regardless of the department). The code I have so far:

TRANSFORM Avg([2014 Rev].[Total%]) AS [AvgOfTotal%]
SELECT [2014 Rev].SBU, Avg([2014 Rev].[Total%]) AS [Total Of Total%]
FROM [2014 Rev] INNER JOIN [2014 Rev_Crosstab with Business %] ON [2014 Rev].SBU = [2014 Rev_Crosstab with Business %].SBU
GROUP BY [2014 Rev].SBU
PIVOT [2014 Rev].SummFunction;

How do I display the calculated field for the final total business % (regardless of department - which is indicated by SBU)?

[June7]

Did you try the CROSSTAB query wizard? It should automatically create a TOTALS field.

[accessnewbie352]

Yes but the query automatically gives me what appears to be the total fields that calculates total by SBU and department. When I do the totals for only SBU in a separate query and then also in an excel file, I get different and higher values, so I believe the cross query is pulling in something additional to give the incorrect calculation.

[accessnewbie352]

i should add that i guess what I am trying to do is ultimately create a form with this crosstab query. I don't mind if I need to create a separate query for the totals (which I tried and the calculations look correct) but is there a way to create a form using values from 2 different queries? I have tried via the expression building and it keeps giving mes like [#TYPE] or [#ERROR]

[June7]

Why would you use a CROSSTAB on a form? Cannot edit the data.

Queries can be joined just like tables can be joined.

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

[accessnewbie352]

I need to create a form to mirror something of a pivot table - I ended up creating the data I need in a query and then linking it to the crosstab and it is working now. Thanks