Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot tables - inserting columns | Excel Worksheet Functions | |||
Blank Cells in Pivot Tables | Excel Discussion (Misc queries) | |||
Macro for Pivot Tables | Excel Discussion (Misc queries) | |||
Product Function in Pivot Tables from Multiple Consolidation Range | Excel Worksheet Functions | |||
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. | Excel Discussion (Misc queries) |