Pivot Table Calculations

[Guest]

I need to perform calculations between rows within an Access PivotTable and I
need some help in figuring out how to do it. I posted the question earlier
and thought I'd better provide more and better background information.

I researched prior posts and all my Access manuals and couldn't find an
answer. I can't imagine there is no way to calculate between rows and
display the results as a row.

For my problem, I'm using a SELECT query to build a PivotTable in Access:

SELECT [Data Table].Position, [Data Table].peg, [Data Table].appn, [Data
Table].mdep, [Data Table].ape, Sum([Data Table].FY03) AS SumOfFY03, Sum([Data
Table].FY04) AS SumOfFY04, Sum([Data Table].FY05) AS SumOfFY05, Sum([Data
Table].FY06) AS SumOfFY06, Sum([Data Table].FY07) AS SumOfFY07, Sum([Data
Table].FY08) AS SumOfFY08, Sum([Data Table].FY09) AS SumOfFY09, Sum([Data
Table].FY10) AS SumOfFY10, Sum([Data Table].FY11) AS SumOfFY11
FROM [Data Table]
GROUP BY [Data Table].Position, [Data Table].peg, [Data Table].appn, [Data
Table].mdep, [Data Table].ape
HAVING ((([Data Table].peg)='tt') AND (([Data Table].appn)='omng'));

The query brings in data for For each position and year. The Position is in
the row area, the years are in the data area, and mdep, appn, and ape are in
the filter area. There are about 1,500 records associated with each
position. Within the PivotTable, I created a total for each position using
the AutoCalc and then hid the details. The PivotTable ends up looking like
this:

Position SumOfYr1 SumOfYr2 SumOfYr3 SumOfYr4
Cycle A Validated 1835 1849 1873 1918
Cycle A Critcial 1576 1468 1477 1555
Cycle A Funded 1415 1301 1277 1306
Cycle B Validated 1835 1849 1868 1925
Cycle B Critical 1576 1468 1499 1574
Cycle B Funded 1340 1322 1337 1336
Cycle C Validated 1983 1849 1868 1925
Cycle C Critical 1457 1346 1349 1457
Cycle C Funded 1234 1232 1233 1231
Grand Total 14341 13684 13781 14227

I want to create a new row called "Cycle A Unfunded". It should display
between the rows "Cycle A Funded" and "Cycle B Validated". The data field
should calculate "Cycle A Funded" minus Cycle A Critical" for each column
(year).

Similarly, I need a new row called "Cycle B Unfunded" between "Cycle B
Funded" and "Cycle C Validated" that performs calculations for each year for
"Cycle B Funded" minus "Cycle B Critical".

And then the same thing for Cycle C to be displayed between "Cycle C Funded"
and the "Grand Total" rows. (In case it matters for calculations, the "Grand
Total" row is meaningless for my data and the values will always be ignored).

I can do all this easily in an Excel pivot, but would like to perform it
within Access. Any help you can provide would be greatly appreciated.

Craig

 

[Dale Fye]

Craig,

Your "Pivot table" is not a true "pivot table, since you have columns for
each of the Fiscal years in your main table. All you really have is an
aggregate query.

Your select statement indicates fields Position, PEG, APPN, MDEP, and APE,
but your "results" below does not include all of these fields. Does your
[Position] field actually contain "Cycle A Validated" and "Cycle A
Critcial"? Personnally, I think I would have set my table up differently,
with a Cycle field (A, B, C), a Funding field ("Validated", "Critical",
"Funded") and a FiscalYear field ("03", "04", "05", etc). You could then
have created the "pivot table" you speak of for any set of years, without
having to add additional columns to your data table for the specific FY.

Having said that, I think you need to create another query to get your
Unfunded figures, then use a union query to join them. The second query
might look something like the following. What I have done here is group by
the first eight characters of the [Position] since it appears that this will
get me "Cycle A ", "Cycle B ", and "Cycle C ", I have then done the
summation of all of the funded and critical items and subtracted the sum of
the critical items from the sum of the funded items. You can expand this to
get the rest of the FY data.

SELECT Left([Position],8) & "Unfunded" AS Unfunded,
Sum(IIf(InStr([Position],"Funded")>0,[FY03],0))-Sum(IIf(InStr([Position],"Critical")>0,[FY03],0))
AS Shortfall_FY03,
Sum(IIf(InStr([Position],"Funded")>0,[FY04],0))-Sum(IIf(InStr([Position],"Critical")>0,[FY04],0))
AS Shortfall_FY04,
Sum(IIf(InStr([Position],"Funded")>0,[FY05],0))-Sum(IIf(InStr([Position],"Critical")>0,[FY05],0))
AS Shortfall_FY05
FROM tbl_BudgetData
GROUP BY Left([Position],8) & "Unfunded";

It doesn't really matter what you call these columns as long as you have the
same number, with the same data type, in the same sequence, in the two
queries that you will use to compose your Union query.

HTH
Dale

I need to perform calculations between rows within an Access PivotTable and
I
need some help in figuring out how to do it. I posted the question
earlier
and thought I'd better provide more and better background information.

I researched prior posts and all my Access manuals and couldn't find an
answer. I can't imagine there is no way to calculate between rows and
display the results as a row.

For my problem, I'm using a SELECT query to build a PivotTable in Access:

SELECT [Data Table].Position, [Data Table].peg, [Data Table].appn, [Data
Table].mdep, [Data Table].ape, Sum([Data Table].FY03) AS SumOfFY03,
Sum([Data
Table].FY04) AS SumOfFY04, Sum([Data Table].FY05) AS SumOfFY05, Sum([Data
Table].FY06) AS SumOfFY06, Sum([Data Table].FY07) AS SumOfFY07, Sum([Data
Table].FY08) AS SumOfFY08, Sum([Data Table].FY09) AS SumOfFY09, Sum([Data
Table].FY10) AS SumOfFY10, Sum([Data Table].FY11) AS SumOfFY11
FROM [Data Table]
GROUP BY [Data Table].Position, [Data Table].peg, [Data Table].appn, [Data
Table].mdep, [Data Table].ape
HAVING ((([Data Table].peg)='tt') AND (([Data Table].appn)='omng'));

The query brings in data for For each position and year. The Position is
in
the row area, the years are in the data area, and mdep, appn, and ape are
in
the filter area. There are about 1,500 records associated with each
position. Within the PivotTable, I created a total for each position
using
the AutoCalc and then hid the details. The PivotTable ends up looking
like
this:

Position SumOfYr1 SumOfYr2 SumOfYr3 SumOfYr4
Cycle A Validated 1835 1849 1873 1918
Cycle A Critcial 1576 1468 1477 1555
Cycle A Funded 1415 1301 1277 1306
Cycle B Validated 1835 1849 1868 1925
Cycle B Critical 1576 1468 1499 1574
Cycle B Funded 1340 1322 1337 1336
Cycle C Validated 1983 1849 1868 1925
Cycle C Critical 1457 1346 1349 1457
Cycle C Funded 1234 1232 1233 1231
Grand Total 14341 13684 13781 14227

I want to create a new row called "Cycle A Unfunded". It should display
between the rows "Cycle A Funded" and "Cycle B Validated". The data field
should calculate "Cycle A Funded" minus Cycle A Critical" for each column
(year).

Similarly, I need a new row called "Cycle B Unfunded" between "Cycle B
Funded" and "Cycle C Validated" that performs calculations for each year
for
"Cycle B Funded" minus "Cycle B Critical".

And then the same thing for Cycle C to be displayed between "Cycle C
Funded"
and the "Grand Total" rows. (In case it matters for calculations, the
"Grand
Total" row is meaningless for my data and the values will always be
ignored).

I can do all this easily in an Excel pivot, but would like to perform it
within Access. Any help you can provide would be greatly appreciated.

Craig