?
Solved

Total on a pivot

Posted on 2015-01-08
6
Medium Priority
?
113 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

719 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