Solved

Access 2010 PivotTable Formulas

Posted on 2011-09-04
6
989 Views
Last Modified: 2012-05-12
I'm trying to figure out how to use the Access 2010 PivotTable Formulas option.  The two options are Create Calulated Total and Create Calculated Detail Field.  

I want to create a Pivot Table that totals a numeric field, when I drag the field into the Pivot Table I get each value instead of a sum.  I thought I could use the Formulas, Create Calculated Total, but it tives me a #Value error if I enter Sum(field) or click the Insert Reference To option.   I do get a value if I type a number or simple formula like 5 + 2.
I've searched the internet and cannot find any help on this subject.  If someone understands how this works, please explain it to me.

I have been able to get it to work if I first create a Calculated Total Field and leave the value a 0 and then drag my numeric field in and resize the other total column to make it invisible, but that doesn't seem like the way it should be done.

Thanks
0
Comment
Question by:ChampagneGal
[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
  • 2
6 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36480989
Perhaps if you posted a smallish sample file, purged of any confidential/sensitive data, that illustrates wht you're trying to do, I or another Expert can help you.

:)
0
 

Author Comment

by:ChampagneGal
ID: 36481869
Thanks for the reply.  I've uploaded the sample database.  It's just a training database and I'm creating the pivot table forms based on the qryPivotTableExample.

The frmPivotTableExample is just the basic shell with the Booking Dates in the left column and the SalesRep Names along the top and nothing in the middle.

The frmPivotTableExample with Total Errors is the example I get when I click on the Formulas command and then select Create Calculated Total.  I have tried typing the name of the field, selecting it from the drop down and clicking the Insert Reference to button and also using the Sum([NoOfPeople]) and each time I get the #VALUE error.

The frmPivotTableExample with Extra Total Column is the only one I can get to work, but I don't think I'm doing it the most efficient way.   I add a Total Column using the Formulas/Create Calculated Total and don't put anything in it, but it does create new columns titled New Total with a value of zero.   Next I drag the NoOfPeople field into the first column under Allen and it inserts a column titled Sum of NoOfPeople with the correct values.   I can hide the New Total column to "fake" it, but I'm not understanding the purpose of the Formulas/Create Calculated Total command.

I hope this makes sense...   Thanks for trying to help me.
fwt-sample.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36484788
It is still not clear what your "exact" expected output should be.
Can you provide some sort of a graphic (markup an existing image, ...etc) to show the "exact" output you need, based on the sample data.

In any event...
<I want to create a Pivot Table that totals a numeric field, when I drag the field into the Pivot Table I get each value instead of a sum. >
After you drag the field in, right-click any value and select: AutoCalc-->Sum
(screencast attached.)


As far a s "calculated fields" go, I sometimes find it easier to add these calculated fields to the underlying query, then simply drop them into the Pivot like any other field.

JeffCoachman
boag2000-496022.flv
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:ChampagneGal
ID: 36490543
Thanks for the video and the comment.   I like your idea of using a query and your suggestion works, but do you know how (or why) someone might use the Formulas - Create Calculated Total or Create Calculated Detail Field?  

Thanks,
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 36490746
<but do you know how (or why) someone might use the Formulas - Create Calculated Total or Create Calculated Detail Field? >

Six of one, half dozen of another....
;-)

But the main reason that a person might opt to create the formulas/Calculated fields in the Pivot itself is that they do not have access to modify the underlying query.
Or perhaps there is only a table behind the Pivot.
Or perhaps they are Pivot experts, and simply prefer to do this in the Pivot.
Finally, there might very well be some functionality in doing this in the Pivot, that is not available with the table/query.

But for most Basic Cases (ex.: Orders, where Price*Quant is needed) creating the calculations in a query, then creating the Pivot from the query would seem the most straightforward.
(...IMHO)

JeffCoachman
0
 

Author Closing Comment

by:ChampagneGal
ID: 36491036
Thank you so much for explaining that, I really appreciate it.
0

Featured Post

Independent Software Vendors: 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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

622 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