Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Total on a pivot

Posted on 2015-01-08
6
Medium Priority
?
114 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

972 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