Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Total on a pivot

Posted on 2015-01-08
6
Medium Priority
?
115 Views
Last Modified: 2015-02-23
I have a pivot table as the record source for a from. the data is Quarterly from 2011.

I want to add a total of the Qtrs for each year and thought I could do it on the form with an expression in a text box on the form but not working for me.

can the total be added to the record source?
TRANSFORM Sum(dw_ddai_parking_permit_productionmonthly.PermitIssued) AS SumOfPermitIssued
SELECT dw_ddai_parking_permit_productionmonthly.YearIssued
FROM dw_ddai_parking_permit_productionmonthly
GROUP BY dw_ddai_parking_permit_productionmonthly.YearIssued
PIVOT dw_ddai_parking_permit_productionmonthly.QTR;

Open in new window

0
Comment
Question by:Brogrim
  • 3
  • 3
6 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40537677
Have that saved as one query (say, Query1), except you need to convert YearIssued to a string

TRANSFORM Sum(dw_ddai_parking_permit_productionmonthly.PermitIssued) AS SumOfPermitIssued
SELECT str(dw_ddai_parking_permit_productionmonthly.YearIssued) as YearIssued
FROM dw_ddai_parking_permit_productionmonthly
GROUP BY str(dw_ddai_parking_permit_productionmonthly.YearIssued)
PIVOT dw_ddai_parking_permit_productionmonthly.QTR;

Open in new window


Then have a second query - let's call that Query2

TRANSFORM Sum(dw_ddai_parking_permit_productionmonthly.PermitIssued) AS SumOfPermitIssued
SELECT "Total" as Year Issued
FROM dw_ddai_parking_permit_productionmonthly
GROUP BY "Total"
PIVOT dw_ddai_parking_permit_productionmonthly.QTR;

Open in new window


and then Union them

SELECT Query1.*
FROM Query1

UNION ALL

SELECT Query2.*
FROM Query2

Open in new window

0
 

Author Comment

by:Brogrim
ID: 40537924
Thanks Philip, that adds up all the Qtrs and gives a Qtrly Total instead of a year total

Example
2011 Qtr3 + 2012 Qtr3 + 2013 Qtr3 + 2014 Qtr3

I want to add

2012 Qtr1 + 2012 Qtr2 + 2012 Qtr3 + 2012 Qtr4
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40537934
Where do you want it? In a new row, or in a new column?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Brogrim
ID: 40538039
The total for the year in a new column
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40538048
Then save what you have got as a query, and then start a new query and say:
[Qtrs] = [2011 Qtr3] + [2012 Qtr3] + [2013 Qtr3] + [2014 Qtr3]
or whatever the field names are.
0
 

Author Comment

by:Brogrim
ID: 40538053
Thanks Philip, I don't have access to the data at the moment. I will try your suggestion ASAP and get back to you. Thanks again
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question