#1   Report Post  
Adam
 
Posts: n/a
Default Pivot Tables, Help?

I am trying to create a pivot table based on an Access table.
when I create the pivot table and link it to the table it asks me to drag
the column fields, the row fields, and data into the proper area. I don't
want any row fields, and i dont quite understand what the data area is-or
what is supposed to go there. I just want to drop the columns in with the
data in each colum then sum them at the bottom.

CAn you please help because my pivot table looks like a mess?
  #2   Report Post  
JulieD
 
Posts: n/a
Default

Hi Adam

the data area is for the information that you want to sum
the columns and rows are used for categories
for example, if you had the following data in an access table
Deptartment............ExpenseType.........Amount

and you wanted a pivot table showing the expenses for each dept you would
drag
department to the column area
expense type to the row area
and amount to the data area
giving you the following

............Sales..........Accounts...........Prod uction ...Total
Meals...100..............50......................3 00............450
Travel....1000.................................... ....................1000
total......1100..............50................... .300..............1450

also check out www.contextures.com for pivot table info

Cheers
JulieD

"Adam" wrote in message
...
I am trying to create a pivot table based on an Access table.
when I create the pivot table and link it to the table it asks me to drag
the column fields, the row fields, and data into the proper area. I don't
want any row fields, and i dont quite understand what the data area is-or
what is supposed to go there. I just want to drop the columns in with the
data in each colum then sum them at the bottom.

CAn you please help because my pivot table looks like a mess?



  #3   Report Post  
Adam
 
Posts: n/a
Default

I got the sheet to layout correctly..however by putting fields i want to sum
in the data area sums them per record across in a row and not at the bottom
of the column like i need. Also when i refresh the data source the records on
the pivot table dont change...I double checked the source and still no
change. I purposely edited the source to see if my sheet would reflect it,
and it does not??

"JulieD" wrote:

Hi Adam

the data area is for the information that you want to sum
the columns and rows are used for categories
for example, if you had the following data in an access table
Deptartment............ExpenseType.........Amount

and you wanted a pivot table showing the expenses for each dept you would
drag
department to the column area
expense type to the row area
and amount to the data area
giving you the following

............Sales..........Accounts...........Prod uction ...Total
Meals...100..............50......................3 00............450
Travel....1000.................................... ....................1000
total......1100..............50................... .300..............1450

also check out www.contextures.com for pivot table info

Cheers
JulieD

"Adam" wrote in message
...
I am trying to create a pivot table based on an Access table.
when I create the pivot table and link it to the table it asks me to drag
the column fields, the row fields, and data into the proper area. I don't
want any row fields, and i dont quite understand what the data area is-or
what is supposed to go there. I just want to drop the columns in with the
data in each colum then sum them at the bottom.

CAn you please help because my pivot table looks like a mess?




  #4   Report Post  
Adam
 
Posts: n/a
Default

It updates it when there is a delete on the uderlying table in access but
when i add them back it does not reflect in the sheet?

"JulieD" wrote:

Hi Adam

the data area is for the information that you want to sum
the columns and rows are used for categories
for example, if you had the following data in an access table
Deptartment............ExpenseType.........Amount

and you wanted a pivot table showing the expenses for each dept you would
drag
department to the column area
expense type to the row area
and amount to the data area
giving you the following

............Sales..........Accounts...........Prod uction ...Total
Meals...100..............50......................3 00............450
Travel....1000.................................... ....................1000
total......1100..............50................... .300..............1450

also check out www.contextures.com for pivot table info

Cheers
JulieD

"Adam" wrote in message
...
I am trying to create a pivot table based on an Access table.
when I create the pivot table and link it to the table it asks me to drag
the column fields, the row fields, and data into the proper area. I don't
want any row fields, and i dont quite understand what the data area is-or
what is supposed to go there. I just want to drop the columns in with the
data in each colum then sum them at the bottom.

CAn you please help because my pivot table looks like a mess?




  #5   Report Post  
JulieD
 
Posts: n/a
Default

Hi Adam

i've not had much experience with pivot tables using MS access, but i set up
a quick one to test out your problem ... i'm using access 2003 and excel
2003.

my db has staff and training courses. When i added a training course to an
existing staff member the pivot table was updated when i pressed the refresh
icon (red exclaimation mark) ... when i added a new staff member and
training course it was again updated when i pressed the refresh icon. - to
create the pivot table i used the create pivot table option on the bottom
left of connection dialog box (data / import external data / new database
query ........ found db, chose tables, said return info the excel and then a
dialog came up and said which cell - on this on was the create pivot table
report link which i pressed).

so what is it that you're doing differently to me, or are you using a
different version (this behaviour may have changed between versions) - i can
test on other versions.

Cheers
JulieD

"Adam" wrote in message
...
It updates it when there is a delete on the uderlying table in access but
when i add them back it does not reflect in the sheet?

"JulieD" wrote:

Hi Adam

the data area is for the information that you want to sum
the columns and rows are used for categories
for example, if you had the following data in an access table
Deptartment............ExpenseType.........Amount

and you wanted a pivot table showing the expenses for each dept you would
drag
department to the column area
expense type to the row area
and amount to the data area
giving you the following

............Sales..........Accounts...........Prod uction ...Total
Meals...100..............50......................3 00............450
Travel....1000.................................... ....................1000
total......1100..............50................... .300..............1450

also check out www.contextures.com for pivot table info

Cheers
JulieD

"Adam" wrote in message
...
I am trying to create a pivot table based on an Access table.
when I create the pivot table and link it to the table it asks me to
drag
the column fields, the row fields, and data into the proper area. I
don't
want any row fields, and i dont quite understand what the data area
is-or
what is supposed to go there. I just want to drop the columns in with
the
data in each colum then sum them at the bottom.

CAn you please help because my pivot table looks like a mess?








  #6   Report Post  
Adam
 
Posts: n/a
Default

Thanks for your help but i figured it out...the problem was that there were
duplicates in the underlying table, the pivot table in excel doesnt show or
count the duplicates...BUT can you help me with another issue?

I'm trying to sum my columns and when i choose table options in the pivot
table options list i check it on to sum - the words "grand total" show up but
not total. What i did then was sum them up manually by highlighting the
entire column and clicking the sum formula button - this works but only
temporarily. Because the total is summed outside the pivot table, when ever
the table grows it total clears out the formula or pushes it down (the
problem with that is if the table shrinks again you cant see the sum because
it was pushed down out of view so you wont even know there is a sum there).

"JulieD" wrote:

Hi Adam

i've not had much experience with pivot tables using MS access, but i set up
a quick one to test out your problem ... i'm using access 2003 and excel
2003.

my db has staff and training courses. When i added a training course to an
existing staff member the pivot table was updated when i pressed the refresh
icon (red exclaimation mark) ... when i added a new staff member and
training course it was again updated when i pressed the refresh icon. - to
create the pivot table i used the create pivot table option on the bottom
left of connection dialog box (data / import external data / new database
query ........ found db, chose tables, said return info the excel and then a
dialog came up and said which cell - on this on was the create pivot table
report link which i pressed).

so what is it that you're doing differently to me, or are you using a
different version (this behaviour may have changed between versions) - i can
test on other versions.

Cheers
JulieD

"Adam" wrote in message
...
It updates it when there is a delete on the uderlying table in access but
when i add them back it does not reflect in the sheet?

"JulieD" wrote:

Hi Adam

the data area is for the information that you want to sum
the columns and rows are used for categories
for example, if you had the following data in an access table
Deptartment............ExpenseType.........Amount

and you wanted a pivot table showing the expenses for each dept you would
drag
department to the column area
expense type to the row area
and amount to the data area
giving you the following

............Sales..........Accounts...........Prod uction ...Total
Meals...100..............50......................3 00............450
Travel....1000.................................... ....................1000
total......1100..............50................... .300..............1450

also check out www.contextures.com for pivot table info

Cheers
JulieD

"Adam" wrote in message
...
I am trying to create a pivot table based on an Access table.
when I create the pivot table and link it to the table it asks me to
drag
the column fields, the row fields, and data into the proper area. I
don't
want any row fields, and i dont quite understand what the data area
is-or
what is supposed to go there. I just want to drop the columns in with
the
data in each colum then sum them at the bottom.

CAn you please help because my pivot table looks like a mess?






  #7   Report Post  
JulieD
 
Posts: n/a
Default

Hi Adam

just to clarify you have a list of categories down the rows and across the
columns and assoicated values in the data area - and when you tick the Grand
Total for columns and grand total for rows the row and column is created
with the heading but no values are shown?

this is most weird ... as in all my tests it shows the grand totals
correctly ... what version of excel are you using? does the same thing
happen if you build a pivot table from "normal" excel data (although i've
tested it successfully on a link to access)
what happens if you apply a report format to your pivot table (the
lightening bolt icon on the pivot table toolbar) does it work then?

Cheers
JulieD



"Adam" wrote in message
...
Thanks for your help but i figured it out...the problem was that there
were
duplicates in the underlying table, the pivot table in excel doesnt show
or
count the duplicates...BUT can you help me with another issue?

I'm trying to sum my columns and when i choose table options in the pivot
table options list i check it on to sum - the words "grand total" show up
but
not total. What i did then was sum them up manually by highlighting the
entire column and clicking the sum formula button - this works but only
temporarily. Because the total is summed outside the pivot table, when
ever
the table grows it total clears out the formula or pushes it down (the
problem with that is if the table shrinks again you cant see the sum
because
it was pushed down out of view so you wont even know there is a sum
there).

"JulieD" wrote:

Hi Adam

i've not had much experience with pivot tables using MS access, but i set
up
a quick one to test out your problem ... i'm using access 2003 and excel
2003.

my db has staff and training courses. When i added a training course to
an
existing staff member the pivot table was updated when i pressed the
refresh
icon (red exclaimation mark) ... when i added a new staff member and
training course it was again updated when i pressed the refresh icon. -
to
create the pivot table i used the create pivot table option on the bottom
left of connection dialog box (data / import external data / new database
query ........ found db, chose tables, said return info the excel and
then a
dialog came up and said which cell - on this on was the create pivot
table
report link which i pressed).

so what is it that you're doing differently to me, or are you using a
different version (this behaviour may have changed between versions) - i
can
test on other versions.

Cheers
JulieD

"Adam" wrote in message
...
It updates it when there is a delete on the uderlying table in access
but
when i add them back it does not reflect in the sheet?

"JulieD" wrote:

Hi Adam

the data area is for the information that you want to sum
the columns and rows are used for categories
for example, if you had the following data in an access table
Deptartment............ExpenseType.........Amount

and you wanted a pivot table showing the expenses for each dept you
would
drag
department to the column area
expense type to the row area
and amount to the data area
giving you the following

............Sales..........Accounts...........Prod uction ...Total
Meals...100..............50......................3 00............450
Travel....1000.................................... ....................1000
total......1100..............50................... .300..............1450

also check out www.contextures.com for pivot table info

Cheers
JulieD

"Adam" wrote in message
...
I am trying to create a pivot table based on an Access table.
when I create the pivot table and link it to the table it asks me to
drag
the column fields, the row fields, and data into the proper area. I
don't
want any row fields, and i dont quite understand what the data area
is-or
what is supposed to go there. I just want to drop the columns in
with
the
data in each colum then sum them at the bottom.

CAn you please help because my pivot table looks like a mess?








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot tables - inserting columns Scott Excel Worksheet Functions 1 March 19th 05 02:07 AM
Blank Cells in Pivot Tables Greg Excel Discussion (Misc queries) 1 March 16th 05 10:23 PM
Macro for Pivot Tables Thomas Excel Discussion (Misc queries) 1 March 15th 05 02:03 AM
Product Function in Pivot Tables from Multiple Consolidation Range bbishop222 Excel Worksheet Functions 0 February 22nd 05 05:55 PM
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. stvermont Excel Discussion (Misc queries) 1 February 17th 05 02:34 AM


All times are GMT +1. The time now is 01:28 PM.

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

About Us

"It's about Microsoft Excel"