Access 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 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==
X-WBNR-Posting-Host: 67.86.86.237
From: "=?Utf-8?B?S2V2aW4gV2l0dHk=?="
Subject: Access Pivot Table INTERACTIVE Help!!!
Date: Tue, 22 Mar 2005 10:23:05 -0800
Lines: 26
Message-ID:
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.access.tablesdbdesign
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGXA03.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.access.tablesdbdesign:90323
X-Tomcat-NG: microsoft.public.access.tablesdbdesign


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, 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]" wrote:

Hi Kevin,

You could send your database file to me at 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

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

Subject: Access Pivot Table INTERACTIVE Help!!!
Date: Fri, 25 Mar 2005 16:43:04 -0800
Lines: 60
Message-ID:
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.access.tablesdbdesign
Path: TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.access.tablesdbdesign:90394
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
X-Tomcat-NG: microsoft.public.access.tablesdbdesign

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]" wrote:

Hi Kevin,

You could send your database file to me at 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

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]" wrote:

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
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,

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



Subject: Access Pivot Table INTERACTIVE Help!!!
Date: Mon, 28 Mar 2005 07:37:02 -0800
Lines: 88
Message-ID:
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.access.tablesdbdesign
Path: TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.access.tablesdbdesign:90454
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
X-Tomcat-NG: microsoft.public.access.tablesdbdesign

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]" wrote:

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
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

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]" wrote:

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



Subject: Access Pivot Table INTERACTIVE Help!!!
Date: Mon, 28 Mar 2005 07:37:02 -0800
Lines: 88
Message-ID:
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.access.tablesdbdesign
Path: TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.access.tablesdbdesign:90454
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
X-Tomcat-NG: microsoft.public.access.tablesdbdesign

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]" wrote:

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
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,

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 . 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...b;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=?="
References:





Subject: Access Pivot Table INTERACTIVE Help!!!
Date: Tue, 29 Mar 2005 22:11:03 -0800
Lines: 210
Message-ID:
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.access.tablesdbdesign
Path: TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.access.tablesdbdesign:90513
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
X-Tomcat-NG: microsoft.public.access.tablesdbdesign

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]" wrote:

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



Subject: Access Pivot Table INTERACTIVE Help!!!
Date: Mon, 28 Mar 2005 07:37:02 -0800
Lines: 88
Message-ID:
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.access.tablesdbdesign
Path: TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.access.tablesdbdesign:90454
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
X-Tomcat-NG: microsoft.public.access.tablesdbdesign

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]" wrote:

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
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

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

Kevin

"William Wang[MSFT]" wrote:

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 . 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...b;en-us;324695

Sincerely,

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 he
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/

If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default...rnational.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=?="
References:







Subject: Access Pivot Table INTERACTIVE Help!!!
Date: Wed, 30 Mar 2005 10:37:04 -0800
Lines: 45
Message-ID:
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.access.tablesdbdesign
Path: TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.access.tablesdbdesign:90552
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
X-Tomcat-NG: microsoft.public.access.tablesdbdesign

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

Kevin

"William Wang[MSFT]" wrote:

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 . 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...b;en-us;324695

Sincerely,

William Wang
Microsoft Online Partner Support