Question about Pivot Tables

[Little Penny]

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.

 

[Guest]

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

 

[danmyers01]

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

 

[Little Penny]

Thanks guys I did it.

One more question
I have a workbook with several pivot table layout just the way I want
it. Can I change the data source to a table or query to an access
database without having to start from scratch?

 

[danmyers01]

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