Access Pivot Table HELP!: calculated columns

G

Guest

There doesn't seem to be much information around about Access Pivot Tables,
and I don't do much in Excel. MS Help is no help whatsoever.

I'm trying to create two columns: average units and average price, and
everything I try comes up with errors. One manual says I should be able to
click on a column (Units), then click on Autocalc and choose a function (like
Avg), but when i click on a column, Autocalc is grayed out. ????

Then if I try to create a calculated field, detail or total, I get the
Properties/ Calculation box, and have used Insert Reference to create a
calculation (NetCharge / Units). However, the results are horribly
inconsistent: sometimes the calculation is correct and sometimes not. (I
have to perform this calculation at a total level, because some entries may
have a charge but no units, as when a charge adjustment has been made. I
can't do the calc at a detail level in the underlying query/table.)

Very frustrating, very confusing, for something which has the potential to
be a very useful tool.

Any help gratefully appreciated.

Kevin
 
T

Tim Ferguson

"=?Utf-8?B?S2V2aW4gV2l0dHk=?=" <[email protected]>
wrote in
I'm trying to create two columns: average units and average price, and
everything I try comes up with errors.

You'll need to provide more information on your data structures: what
fields do you have and how are you grouping the data?

Very frustrating, very confusing, for something which has the
potential to be a very useful tool.

What, the newsgroup or the pivot table?

Tim F
 
G

Guest

Hi, Tim -

I have (among others), OrderDate, BillTo, Product, Units, and Extension.
One of the things I'd like to see is OrderDate as rows, Product as column
groups, and total units, average units, and average price under each product
(average units being total units / order count, average price being extension
/ units). Not too exotic, I would think, but I sure can't figure out how to
do it.

Thanks,

Kevin

:
 
T

Tim Ferguson

I have (among others), OrderDate, BillTo, Product, Units, and
Extension. One of the things I'd like to see is OrderDate as rows,
Product as column groups, and total units, average units, and average
price under each product

Okay: I set up your table as indicated, and the following seems to work:

TRANSFORM Sum(Units) / Count(*) AS AvgOrder
SELECT ProductsForPivot.OrderDate
FROM ProductsForPivot
GROUP BY ProductsForPivot.OrderDate
PIVOT ProductsForPivot.Product;

You can of course change the TRANSFORM line to some other calculation if
you need: I did not have a source for price, but if it's in your
datasource then you can do something like

TRANSFORM Sum(ProductPrices.Price)/Sum(UnitsSold) AS AvgPrice
SELECT etc etc

Remember that there can only be one value in the value cell at a time, so
you'll have to re-run the query. I am not a pivot table guru, as I don't
have a particular interest in financial databases. I guess I would
probably transport the whole lot to something like Excel, where the
numerical manipulation becomes trivially easy and the output and
presentation functions are so much better too. It's probably not too hard
to set up a little bit of code to get the data straight from the mdb file
without bothering with Access at all.

I am not sure if I have answered your question or not?

All the best


Tim F
 
G

Guest

Hi, Tim -

Thanks for the help, but these are pivot tables that the user is playing
with interactively, and I'm looking for the way to construct averages and
calculated fields in that way, through the pivot table interface itself.
Seems like there ought to be a way? The interface seems to have ways to do
this, (AutoCalc and Calculated Details/Totals) but I can't get them to work.

Kevin
 
T

Tim Ferguson

The interface seems to have ways to do
this, (AutoCalc and Calculated Details/Totals) but I can't get them to
work.

Like I said, I am no pivot table expert, and even less of one on the UI.
What if you set up the pivot on a query that included fields based on
appropriate DCount() or DSum() calculations? If the query runs like sausage
meat, it would probably be an appropriate use of a temporary table,
particularly if other people are doing updates at the same time.

Hoping someone else will butt in here...!

All the best


TimF
 
G

Guest

I'm already using queries to create temporary tables to run these from.
Unfortunately some line items may contain extension adjustments without a
quantity, so calculating average price as extension / quantity at detail
level is impossible, and I'm not sure how to deal with this.

Like I said at the beginning of this thread, I'm finding Access pivot tables
(except for these "minor" problems) to be incredibly useful, and I'm
surprised there's not more information or expertise in using them out there.
I've done routines which allow users to revise a pivot table from the
interactive screen and name and save it for later use, which they love, but
they'd really like to be able to do averages and calculated fields. With
those abilities, these would be a wonderful answer to user-driven flexible
reporting.

Kevin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top