 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / May 2005Access Pivot Table INTERACTIVE Help!!!
> There doesn't seem to be much information around about Access Pivot Tables, > and I don't do much in Excel. I'm specifically looking for how to do this > interactively, using the Access Pivot table interface, so the users can > develop their own reports. I've developed routines to let them name and save > their changes to a set of "base" pivot tables. > > 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 Hi Kevin, You could send your database file to me at v-rxwang@microsoft.com so that I'll be able to work on this issue in an efficient manner. If you would not like to that, it is best that you post the detailed reproduce steps with the sample database Northwind so that all people in this community can understand what the exact problem you are encountering. I look forward to hearing from you. Sincerely, William Wang Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- >Thread-Topic: Access Pivot Table INTERACTIVE Help!!! >thread-index: AcUvDDCIxoFqffC9QxaX/DS7tdxlLw== [quoted text clipped - 44 lines] >> >> Kevin Hi, William - Thanks for the response. Let's start with the real basics: Northwind Query Invoices. (I'm not going to say anything about how badly NorthWind needs an update. Oh, I just did. Sorry.) I'd like to do a pivot table which has Company Name as rows and Shipped Date by Month as columns. I dragged Company Name as a row, and Shipped Date By Month as a column. (It changed the label to Years, which isn't very informative, but that's not your problem, it's MS's, and I do know how to change it, it just wasn't a good design decision by MS.) Under Shipped Date By Month, I'd like several columns: Total Qty, Average Qty, Total Qty * UnitPrice, and Average (SumOf(Extended Price)) / Total Qty). Seems straightforward, but I sure as hell haven't been able to figure out how to do it interactively, from the Pivot Table screen. There is an additional problem I have in my own data, which NorthWind may or may not have: Extended Price for a given record may have a zero Qty, when the Extended Price is an adjustment, so at a detail level, Extended Price / Qty is a divide by 0, but I need it at the Total level. If you can tell me what buttons to click and what to drag to make this happen, I'll be able to solve all my problems. (One (of the many) things I haven't been able to figure out is, in the field list, each field has a +, and under it, an indented repetition of the same name. I have no idea what they represent, and no Help I've brought up has explained them.) This is probably one of those things like "how to tie a shoe", where a demonstration is easier than a thousand words, but unfortunately MS hasn't made those available on-line yet, But it may also be one of those things like my inability to get recognized on this forum where I'd done everything pretty much right, and it still didn't work. (Thanks, Mitch!) I think Access Pivot Tables are vastly underappreciated, but the problems I'm having may be why. I'll appreciate any help you can give. Kevin > Hi Kevin, > [quoted text clipped - 10 lines] > William Wang > Microsoft Online Partner Support Hi Kevin, I can add Total Qty and Average Qty, but I cannot manage to add Average (SumOf(Extended Price)) / Total Qty. To add the first two fields, we can follow these steps: 1. Select Quantity from the fields list, and then select Detail Data from the drop down list at bottom. Click the Add to button. 2. Right click Quantity in the Detail area, and then point to AutoCalc, select Average. 3. Right click Quantity in the Detail area, and then point to AutoCalc, select Sum. HTH! Sincerely, William Wang Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- >Thread-Topic: Access Pivot Table INTERACTIVE Help!!! >thread-index: AcUxnMUJfTDAXf9NRx+pYpebGimGEg== >X-WBNR-Posting-Host: 67.86.86.237 >From: "=?Utf-8?B?S2V2aW4gV2l0dHk=?=" <kdw@newsgroup.nospam> >References: <AE10F1A1-02CD-47FA-A817-3F42C89B28CD@microsoft.com> <xiSOKP1LFHA.380@TK2MSFTNGXA02.phx.gbl> >Subject: RE: Access Pivot Table INTERACTIVE Help!!! >Date: Fri, 25 Mar 2005 16:43:04 -0800 [quoted text clipped - 72 lines] >> William Wang >> Microsoft Online Partner Support Hi William - I was afraid of this: it works in Northwind, but not in my table. If I click on Units in the Field list and click on Add to detail area, it doesn't appear. Can I upload a 6mb file here? I can send mine to you. I can strip off some of the data if I need to, but I'm not sure we'll see the same results. Am I not going to be able to display average price? And can you tell me the difference between + Units and Units (under it) in the Field list? Thanks for the response. Kevin > Hi Kevin, > [quoted text clipped - 65 lines] > > > >Kevin Hi Kevin, >I was afraid of this: it works in Northwind, but not in my table. If I >click on Units in the Field list and click on Add to detail area, it doesn't >appear. Can I upload a 6mb file here? I can send mine to you. I can strip >off some of the data if I need to, but I'm not sure we'll see the same >results. I'm sorry that it is not appropriate to troubleshoot database-specific issues such as this via a newsgroup thread. Based on the current status of this issue, it is best that you open a Support incident with Customer Service and Support (CSS) so that a dedicated Support Professional can work with you in a more timely and efficient manner. For a complete list of Microsoft Product Support Services phone numbers, please go to the following address on the World Wide Web: <http://support.microsoft.com/directory/overview.asp> If you are outside the US please see http://support.microsoft.com for regional support phone numbers. >Am I not going to be able to display average price? Normally if we have a price field, we should be able to add this field to the Detail area, right-click this filed and then point to AutoCalc, then click Average. >And can you tell me the >difference between + Units and Units (under it) in the Field list? There is no difference between them. But if you expand Order Date By Month or Order Date By Week, you will see year, month, etc. These two field sets give us the ability to group data by a particular portion of the date. If anything is unclear, feel free to let me know. Sincerely, William Wang Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- >Thread-Topic: Access Pivot Table INTERACTIVE Help!!! >thread-index: AcUzq/0THzgdyupHQjGciaq70+ykzA== >X-WBNR-Posting-Host: 67.86.86.237 >From: "=?Utf-8?B?S2V2aW4gV2l0dHk=?=" <kdw@newsgroup.nospam> >References: <AE10F1A1-02CD-47FA-A817-3F42C89B28CD@microsoft.com> <xiSOKP1LFHA.380@TK2MSFTNGXA02.phx.gbl> <B6C7F012-6AE6-4160-B89C-339B7BBF797F@microsoft.com> <uRV00h4MFHA.3720@TK2MSFTNGXA03.phx.gbl> >Subject: RE: Access Pivot Table INTERACTIVE Help!!! >Date: Mon, 28 Mar 2005 07:37:02 -0800 [quoted text clipped - 99 lines] >> > >> >Kevin Now I'm thoroughly confused. In an earlier message you encouraged me to send you my database, and now you tell me that it's not appropriate to troubleshoot specific databases. Isn't there a conflict here? Additionally, if, as you said, there's no difference between the + field and the one below it, why do they both display? (Again, this isn't really a question for you, but for the MS designers. I'm not trying to beat you up) And, I'm afraid, additionally, the file I wanted to send you is a single table, so it's hardly "database specific". It's just a single table which doesn't work like NorthWind does, apparently. I don't understand why it doesn't, and I'd like to. The more I look at the differences between Excel Pivot tables and Access Pivot tables, the more I appreciate what Access Pivot tables can do, with a little more work on the part of the designers. They were absolutely briiliant... they just left out a few things, like the easy addition of an average column, and of calculated fields. You really can't appreciate this until you've tried. Still trying... Kevin > Hi Kevin, > [quoted text clipped - 171 lines] > >> > > >> >Kevin Hi Kevin, I appologize for any confusion my responses may have caused you. When I first asked for a copy of your database, I'm not sure whether or not this issue is database-specific. In which case, working on your database would be more efficient than working on my own to get a picture of what happened. However from your post submitted on Mar 28th, I thought this issue is database-specific because we cannot reproduce the issue with the sample Northwind database. For database-specific issues, we commonly recommend contacting CSS directly rather than working on a newsgroup thread because Customer Service and Support (CSS would work on such issues in a most efficient manner. If you would like to continue troubleshooting here, I would be happy to be of further asisstance. But I hope you understand that I didn't mean to bounce you between support professionals by redirecting you to CSS, I only want you to be at the best place for resolving this particular issue. For now, you can compress your database to a .zip file and email it directly to me at v-rxwang@microsoft.com. I assume that you only want two columns: average units and average price. If you still have any other requests, please let me know. As for your concern with the + field and the one below it, I believe it is designed because all fields will look consistent. After all, + Order Date By Week is different from the ones below it. The plus sign (+) is meanful for those date grouping fields. Additionally, there is a webcast you might find helpful: Support WebCast: Overview of PivotTables and PivotCharts in Microsoft Access 2002 http://support.microsoft.com/default.aspx?scid=kb;en-us;324695 Sincerely, William Wang Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- >Thread-Topic: Access Pivot Table INTERACTIVE Help!!! >thread-index: AcU070B6jOURpWSBThqrw0cc2XNqRg== >X-WBNR-Posting-Host: 67.86.86.237 >From: "=?Utf-8?B?S2V2aW4gV2l0dHk=?=" <kdw@newsgroup.nospam> >References: <AE10F1A1-02CD-47FA-A817-3F42C89B28CD@microsoft.com> <xiSOKP1LFHA.380@TK2MSFTNGXA02.phx.gbl> <B6C7F012-6AE6-4160-B89C-339B7BBF797F@microsoft.com> <uRV00h4MFHA.3720@TK2MSFTNGXA03.phx.gbl> <E0157A7F-8832-4955-9BCA-651D8F5B5AD1@microsoft.com> <AWyFUiENFHA.1016@TK2MSFTNGXA03.phx.gbl> >Subject: RE: Access Pivot Table INTERACTIVE Help!!! >Date: Tue, 29 Mar 2005 22:11:03 -0800 [quoted text clipped - 214 lines] >> >> > >> >> >Kevin Thanks, William. I'll email it to you. Kevin > Hi Kevin, > [quoted text clipped - 33 lines] > William Wang > Microsoft Online Partner Support Hi Kevin, If what you want is the "Average of Units" and "Average of NetCharges", you need to add the Units filed and the NetCharges field to the Detail Data area. After opening your tale in PivotTable view and clicking the Show Details button on the PivotTable toolbar, I found that you only have Units field. You can then follow these steps to acheive what you want: 1. Select NetCharges from the fields list, select Detail Data from the dropdown list and click the "Add to" button. 2. Now you have two fields in the detail area: Units and NetCharges. 3. Right-click on Units and point to AutoCalc and click Average. 4. Right-click on NetCharges and point to AutoCalc and click Average. 5. Click the Hide Details button on the PivotTable toolbar. You will find the "Average of Units" and "Average of NetCharges". I've sent the modified database file to you via e-mail. Feel free to let me know if you have any further questions. Sincerely, William Wang Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== Business-Critical Phone Support (BCPS) provides you with technical phone support at no charge during critical LAN outages or "business down" situations. This benefit is available 24 hours a day, 7 days a week to all Microsoft technology partners in the United States and Canada. This and other support options are available here: BCPS: https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469 Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/ If you are outside the United States, please visit our International Support page: http://support.microsoft.com/default.aspx?scid=%2finternational.aspx. ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- >Thread-Topic: Access Pivot Table INTERACTIVE Help!!! >thread-index: AcU1V3g1NgpAu4Q/RGGum/hrbb362A== >X-WBNR-Posting-Host: 67.86.86.237 >From: "=?Utf-8?B?S2V2aW4gV2l0dHk=?=" <kdw@newsgroup.nospam> >References: <AE10F1A1-02CD-47FA-A817-3F42C89B28CD@microsoft.com> <xiSOKP1LFHA.380@TK2MSFTNGXA02.phx.gbl> <B6C7F012-6AE6-4160-B89C-339B7BBF797F@microsoft.com> <uRV00h4MFHA.3720@TK2MSFTNGXA03.phx.gbl> <E0157A7F-8832-4955-9BCA-651D8F5B5AD1@microsoft.com> <AWyFUiENFHA.1016@TK2MSFTNGXA03.phx.gbl> <DAEE206F-7BDC-42EC-8B94-CCAB400943FF@microsoft.com> <4l2dOkQNFHA.2360@TK2MSFTNGXA03.phx.gbl> >Subject: RE: Access Pivot Table INTERACTIVE Help!!! >Date: Wed, 30 Mar 2005 10:37:04 -0800 [quoted text clipped - 56 lines] >> William Wang >> Microsoft Online Partner Support Kevin, William, I have had a similar problem where the AUTOCALC is GRAYED out for a PivotTABLE. The funny thing is that it works for a PivotCHART. Sometimes I can get it to work by switching to the PivotCHART, add the data (it automatically comes up as COUNT) then switch to PivotTABLE. Sometimes it keeps the COUNT sometimes it drops the item i added. William, maybe you can make a suggestion for my problem as well? The table I am using is extremely simple. The table has only one field called "Date" and it contains a population of dates between 1/1/05 and 5/27/05 (many are duplicates). My goal here is to have a pivot table that gives me the total COUNT for each date (How many times does each date appear). I can easily do a pivot chart and add the dates to the "category" (at the bottom) and I add the payment date to the "data" field in the middle. It automatically adds "Count of Date" and shows me the data I need in chart form. I can also right-click on "Count of Date" and select Autocalc in order to change the calculation type if I want (to sum, min, max, avg...etc). Now when I change the view to PivotTABLE, it kept the "Count" total that I had done in the PivotChart (it doesnt always keep it). I removed the fields and started from scratch. Add the Dates to ROW, then when I add Date to Data -- it automatically creates "SUM of DATE" - which is a irrelevant number. If I right-click, the AUTOCALC is once again grayed out. I also tried this with the Date_By_Week (fields automatically created by Access on the Field List) and the same problem occurs. If I could just use the AUTOCALC field like I can on the PivotCHART, then there would be no problem. Is there a specific problem that causes the AUTOCALC option to become greyed out? Thanks very much!! Jeff > Hi Kevin, > [quoted text clipped - 131 lines] > >> William Wang > >> Microsoft Online Partner Support |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.