Access Pivot Table INTERACTIVE Help!!!

[Guest]

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

 

[William Wang[MSFT]]

Hi Kevin,

You could send your database file to me at (e-mail address removed) 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.

--------------------

 

[Guest]

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

 

[William Wang[MSFT]]

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=?=" <[email protected]>
References: <[email protected]>

 

[Guest]

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

 

[William Wang[MSFT]]

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=?=" <[email protected]>
References: <[email protected]>

<[email protected]>

 

[Guest]

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

 

[William Wang[MSFT]]

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 (e-mail address removed). 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=?=" <[email protected]>
References: <[email protected]>

<[email protected]>
<[email protected]>
<[email protected]>

 

[Guest]

Thanks, William. I'll email it to you.

Kevin

 

[William Wang[MSFT]]

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=/international.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=?=" <[email protected]>
References: <[email protected]>

<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>

 

[Guest]

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