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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"=?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
|
|||
|
|||
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
|
|||
|
|||
"=?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
|
|||
|
|||
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
|
|||
|
|||
"=?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
|
|||
|
|||
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 | |
|
|
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 |