A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access Pivot Table HELP!: calculated columns



 
 
Thread Tools Display Modes
  #1  
Old March 9th, 2005, 07:37 PM
Kevin Witty
external usenet poster
 
Posts: n/a
Default Access Pivot Table HELP!: calculated columns

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
  #2  
Old March 13th, 2005, 02:35 PM
Kevin Witty
external usenet poster
 
Posts: n/a
Default

Still looking for answers.

"Kevin Witty" wrote:

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

  #3  
Old March 13th, 2005, 07:35 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"=?Utf-8?B?S2V2aW4gV2l0dHk=?="
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

  #4  
Old March 13th, 2005, 08:47 PM
Kevin Witty
external usenet poster
 
Posts: n/a
Default

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

"Tim Ferguson" wrote:


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


  #5  
Old March 14th, 2005, 06:41 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"=?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

  #6  
Old March 14th, 2005, 07:53 PM
Kevin Witty
external usenet poster
 
Posts: n/a
Default

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


  #7  
Old March 15th, 2005, 05:43 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

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

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

  #8  
Old March 15th, 2005, 06:33 PM
Kevin Witty
external usenet poster
 
Posts: n/a
Default

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


"Tim Ferguson" wrote:

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

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mixed up with Relationships..help! KrazyRed New Users 3 January 26th, 2005 06:03 AM
Pivot Table Access 2000? Air-ron General Discussion 2 October 29th, 2004 06:19 PM
Pivot Table Calculated Fields ?? Corey General Discussion 1 September 23rd, 2004 12:11 AM
Pivot table drops columns on refresh Jason General Discussion 1 July 1st, 2004 02:18 PM


All times are GMT +1. The time now is 09:49 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.