Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about Pivot Tables
Is it possible to have a Pivot table in Excel use a table in an Access
Database as it source data? If so can some one point me in the direction that can teach me how to set this up. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about Pivot Tables
That is one of the real advantages of pivot tables is that they can link
directly to databases which gets you around that pesky 65,536 rows limit. It is easy to do. Start your pivot table as normal but when you get to the section on Data Source (Step 1) change the option button to External Data. Choose Get Data. From there you can select Access and then browse for the database you want. At that point you select either the table or query that you need and you are good to go... Your only limit is memory so you can easily bring back a couple of hundred thousand records (millions even) and the preformance is surprisingly good... -- HTH... Jim Thomlinson "Little Penny" wrote: Is it possible to have a Pivot table in Excel use a table in an Access Database as it source data? If so can some one point me in the direction that can teach me how to set this up. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about Pivot Tables
On Sep 27, 4:19 pm, Little Penny wrote:
Is it possible to have a Pivot table in Excel use a table in an Access Database as it source data? If so can some one point me in the direction that can teach me how to set this up. From the Menu options, choose Data Pivot Table and Pivot Chart Report. Then in the dialog box that appears, choose 'External data source'. The next box asks where the data is stored. Press the 'Get Data' button. In the next dialog box that pops up, from the Databases tab choose 'MS Access Database*' and then press okay. Then in the following dialog box find the path to the file that contains the table you want to 'link' to and press Okay. Then choose the table or query (both should be available). You also have the option of which fields to include. Most of the time, you can just click next a few times to get through the next screens. When you have the option to press finish, do so. That should get you back to the Pivot Table wizard where you can press Next or Finish, depending on how you prefer to set up the pivot table. Hopefully this helps. Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about Pivot Tables
You can certainly try, though there are various reasons it may not
work well. You need to pull up the pivot table wizard (right click on the pivot table and select the option). Then hit 'Back' until you have the option to 'GetData'. Then you should be able to follow the steps outlined previously. One of the hurdles to being sure the layout stays the same is that the fields are the same and that they are the same type (number vs string). Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Tables - DisplayEmptyColumn question | Excel Programming | |||
Pivot Tables - DisplayEmptyColumn question | Excel Programming | |||
Pivot Table Question: SubTotals for 2 of 4 Pivot Tables in same worksheet | Excel Programming | |||
Question about Pivot Tables..why is that | Excel Discussion (Misc queries) | |||
Question About Pivot Tables | Excel Programming |