Solved

Total on a pivot

Posted on 2015-01-08
6
106 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
Comment Utility
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
Comment Utility
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
Comment Utility
Where do you want it? In a new row, or in a new column?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Brogrim
Comment Utility
The total for the year in a new column
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now