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

"Tim Ferguson" wrote:

"=?Utf-8?B?S2V2aW4gV2l0dHk=?="
wrote in :

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