Creating a calculated field in my pivot table

[Repent]

I'm trying to create a totals value in my pivot table.
I am drawing from a query that has several fields, the ones used in the math are:
LF Run--how much material was run
LF Produced--how much became finished product
Waste--difference between LF Run and LF Produced is the amount of waste
Waste%--Waste divided by LF Run gives percentage of overall waste
I have four columns and three of the columns are set to sum total themselves (LF Run, LF Produced, Waste). What I need to do is create a formula (in the pivot table itself?) that will produce the results of Waste%=Waste/LF Run and to place that answer in the column for Waste%, in the total area.


I can get this to work for each individual record that makes up the totals in the pivot table because in the query, in the field name box under design mode, I've typed in "Waste%: [Waste]/[LF Run]" in the field name box so when the query runs, it does the math on the fly and provides the answer for each record. Of course, when I collapse the details, to only see summary in the pivot table, there are sum totals for LF Run, LF Produced, and Waste, just not Waste%.


I've tried creating a calculated field in the pivot table, by selecting, from the Design tab, formulas, create calculated detail field, and on the calculations tab I've typed in every value I can see available to me, such as Sum of Waste (Total)/Sum of LF Run (Total) and many variations. I've also used brackets but still get a ##error message in the column.


Unfortunately I cannot upload a pic with this post to show visually what I mean. I hope I'm being clear.
Please help me with this. I will respond with as much additional info as I can.


Thanks;
Chris

 

[boblarson]

I don't know if you have handled possible nulls in there or zeroes for dividing:

Waste%: IIf([LF Run] > 0,Nz([Waste],0)/[LF Run],0)

 

[Repent]

I don't know if you have handled possible nulls in there or zeroes for dividing:

Waste%: IIf([LF Run] > 0,Nz([Waste],0)/[LF Run],0)

Hello Bob from Portland. I'm down here in Bend. I was hoping someone of your caliber would answer me. I've seen many of your posts and it's obvious you know what your talking about.

I plugged your code into the field under Query design and ran the query as a pivot table view. The query runs without error and produces results similar to summing up each record. I'm still not getting the desired result and most likely it is because I'm not explaining the situation properly.

I've posted two jpg of the pivot table output and design view with markup on what it is I'm looking for. If you would take another look please?

chris

 

[boblarson]

Unfortunately I don't use the pivot table feature all that much so I would have to play with it a bit to see about getting it. And I'm a bit short on time today. Hopefully someone else might chime in.

 

[Repent]

Unfortunately I don't use the pivot table feature all that much so I would have to play with it a bit to see about getting it. And I'm a bit short on time today. Hopefully someone else might chime in.

nooooooooooooo!!!!!!!!!

 

[Repent]

if someone could point me in the right direction?

 

[Repent]

if someone could point me in the right direction?

bump to the top

 

[Banana]

Unforunately, I'm unfamiliar with pivot tables. If I've understood your requirement, I believe it probably is best accomplished using Excel to report on the data because my hunch is that the PivotTable does not provide the control you want from it where it's very easy to do in an Excel spreadsheet. If you can use Excel (I believe there's support for embedding Excel Spreadsheet inside an Access form, BTW), I think you will find it much easier to control specific columns/rows with the trade off of doing a bit more of VBA but that shouldn't be too bad.