Solved

Total on a pivot

Posted on 2015-01-08
6
108 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

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

Accepted Solution

by:
Phillip Burton earned 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

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…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

809 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