Excel Pivot Tables from Access source (W2k Office 2k)

Topic

New Reply

I had some preposterous spreadsheets (62000 rows and very many columns) from which I ran pivot tables. Deep problems with recalculations of many lookups even when I replace all but one of each with the resulting values.

So I have just migrated the source information into a normalised access database. At ecery step I satified myself that the excel functions still allowed me to do the analysis I wanted, and I was quite pleased with myself…

Until

Despite my test queries in Access and my test pivots in Excel I find I have aproblem.

a number of my related tables in the access model are to bring in user friendly descriptions rather than the unfriendly codes int he main data tables. Many of the fields are null, that is the information is populated progressively, so they start off empty and gain values through time. My tests of queries in access worked fine, the ew field I tried to “pivot” also worked fine.

So when the crunch comes, I find that Microsoft Query, which I believe that Excel uses to get the data out of Access, wont allow outside joins with more than two tables. Inside joins with null fields effectively zapp all the valid information. I just cant work out how to run an Access query and make the results available to Excel pivot table routines.

Am I in trouble, or is there a trick to this?

Thanks for your experience

Mike C

Reply | Quote

Replies

Try creating a query in the Access database that gathers all the data you need. I successfully created a pivot table in Excel 2002 based on an Access query that in its turn was based on 6 tables, with 5 outer joins.

Reply | Quote

Hans, hello again and thank you,

I tried that after I made my post, and tried to use the Access Query as the source. My inexperience showed when I couldnt get at the query from excel.

My query was saved in my access databasse. The pivot table “get data” dialogue offers queries as the source, but only seems to list queries that are somehow stored outside Access databases – not even sure that they are access queries.

How do I stitch the two worlds together?
Reards,

Mike C

Reply | Quote

Mike,

When you click Get Data… in step 2 of the Pivot Table wizard, the Choose Data Source dialog is displayed. Don’t activate the Queries tab, for – as you found out – this only lists queries stored as separate files. You need the Databases tab. Select an Access data source; the default one is named “MS Access Database.”
If you don’t see that, select and click OK. In the Create New Data Source dialog, type a name, for example Access, then select “Microsoft Access Driver (*.mdb)” from the list, click Connect…, select your database and click OK. If you wish, you can specify the query here, but that is not necessary. Click OK to create the data source, then continue.

Once you have selected the data source in the Databases tab of the Choose Data Source dialog, click OK. You may have to select the database now, depending on how the data source has been set up. Then, the Query Wizard will start.

If you don’t see your query in the Query Wizard, click the Options… button (see screenshot). This will open the Table Options dialog. Make sure that the Views check box is ticked – views are what Access calls queries. Once you click OK, “Available tables and columns” in the Query Wizard will list queries in the Access database as well as tables.

Reply | Quote

Mike,

When you click Get Data… in step 2 of the Pivot Table wizard, the Choose Data Source dialog is displayed. Don’t activate the Queries tab, for – as you found out – this only lists queries stored as separate files. You need the Databases tab. Select an Access data source; the default one is named “MS Access Database.”
If you don’t see that, select and click OK. In the Create New Data Source dialog, type a name, for example Access, then select “Microsoft Access Driver (*.mdb)” from the list, click Connect…, select your database and click OK. If you wish, you can specify the query here, but that is not necessary. Click OK to create the data source, then continue.

Once you have selected the data source in the Databases tab of the Choose Data Source dialog, click OK. You may have to select the database now, depending on how the data source has been set up. Then, the Query Wizard will start.

If you don’t see your query in the Query Wizard, click the Options… button (see screenshot). This will open the Table Options dialog. Make sure that the Views check box is ticked – views are what Access calls queries. Once you click OK, “Available tables and columns” in the Query Wizard will list queries in the Access database as well as tables.

Reply | Quote

Hans, hello again and thank you,

I tried that after I made my post, and tried to use the Access Query as the source. My inexperience showed when I couldnt get at the query from excel.

My query was saved in my access databasse. The pivot table “get data” dialogue offers queries as the source, but only seems to list queries that are somehow stored outside Access databases – not even sure that they are access queries.

How do I stitch the two worlds together?
Reards,

Mike C

Reply | Quote

Try creating a query in the Access database that gathers all the data you need. I successfully created a pivot table in Excel 2002 based on an Access query that in its turn was based on 6 tables, with 5 outer joins.

Reply | Quote