Pivot Tables, Help?

A

Adam

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

JulieD

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...........Production ...Total
Meals...100..............50......................300............450
Travel....1000........................................................1000
total......1100..............50....................300..............1450

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

Cheers
JulieD
 
A

Adam

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

Adam

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

JulieD

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
 
A

Adam

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).
 
J

JulieD

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
 
Top