Excel 2007: Build Pivot Table with MS ACCESS Query (View)

[KurtVba]

Hi to that special MS Excel/Access Guru,

My problem probably can be solved with a simple, but important click somewhere. I have surfed about 6.5 hours to find this click to no avail.

MY GOAL
is to use Excel 2007 Pivot Tables to summarize the filtered data records found in a MS Access 2007 QUERY. Thus I can harness the power of Excel Pivot Table functions with the efficiency of Access's database organization (memory optimization plus indexing speed features). According to Mr. Excel in his book PIVOT TABLE DATA CRUNCHING book for Excel 2007, this should be a couple of clicks.

THE PROBLEM:
I tried building an Excel Pivot Table using an Access query (view). I can build the link and populate the fields in the Excel Pivot Table, but I get zero (0) records (rows of data) in the Pivot Table. The query I am trying to connect to in Access works perfectly in Access.

TAB STEPS IN EXCEL
which I followed are found in Mr. Excel's book on page 180: Data, From Access, Selected database, Select table or view (query).

THE UNDESIRED RESULT
I have been able to get a TABLE (but again, not the QUERY records) out of MS Access into the Excel Pivot Table.

ALTERNATIVE APPROACH (tried without success)
I also tried the other way suggested in Mr. Excel's book on EXCEL 2007. I used the tabs: Insert, Pivot Table, Use external data source, Connections, Browse for More, Select data source, Select Table or View (i.e. Query). I also was able to get the TABLE records (data rows) in Access, but not those of the QUERY, into Excel's Pivot Table.

Both the Excel and Access files were in the Trust Center, as Mr. Excel recommended. I wish to avoid using the old MS Query function alternative at this time--simple things for simple minds.

There is probably a simple click solution somewhere. Who knows how to get the Access (2007) QUERY records into an 2007 Excel Pivot Table?

Thank you for you time and advice in advance!
Kurt

 

[SydneyGeek]

It should just work; I did a test run on a query and got data. However...
If you have criteria or parameters in the query, that could be your issue; I don't think the pivot table will process the parameters.

Denis

 

[KurtVba]

Re: Build Excel 2007 Pivot Table with MS ACCESS Query (View)

Hi SydneyGeek,

You were up early! Thanks for the reply.

I used a simple criteria: LIKE "*TestSomething*" in the Access Query to filter for just the data I needed for the Excel Pivot Table. I did not use an Access Parameter Query.

Could you try out some simple criteria in your Query?

Thanks!
Kurt

 

[KurtVba]

Hi SydneyGeek,

I tried the Access Query without any criteria in it, and it worked with Excel Pivot Tables. So the lesson for me is: using the Access Query through Excel is limited to just choosing the desired fields (columns). It cannot be used to limit the records (rows of data).

I used Excel's MS Query now to filter for the desired records (rows).

Thanks SydneyGeek for your help! I appreciated it.
Kurt

 

[TomRenish]

I see this problem too. The results can be oddly random; sometimes the pivot table won't populate at all, other times it selects everything from the query and acts as though the "like" statements were never a part of the query.

 

[SydneyGeek]

I have used two methods for filtering Access data from Excel. One is to create a query in VBA and use that query to pull data from Access into a worksheet; the other is to use MS Query (Get external data). The MS Query option is easier to set up than code, and you can use parameters with the query. Base the pivot table on the query data in the worksheet.
What both methods share in common is that the filtering is controlled from Excel, rather than relying on criteria in the Access database.

Denis

 

[KurtVba]

Hi Denis,

Thanks for your comments. I appreciate them, especially after I found you in Mr. Excel's EXCEL GURUS GONE WILD book.

It looks to me like MS missed a big marketing opportunity for their Excel / Access programs. Without importing the Access data into Excel tables, I thought I could easily use the front end presentation power of Excel with the back end power (memory optimization, speed through indexing) of Access to get fast and easy results through just linking to an Access Query which includes my filtering Criteria to create an Excel Pivot Table. How easy could that have been?

I solved my problem with MS Query. Out of curiosity, do you have some simple VBA code to create the Access Query, or maybe a web link?

Thanks,
Kurt

 

[Fazza]

Is it just the wildcard. Viz, LIKE "%whatever%" instead of LIKE "*whatever*" ?

 

[KurtVba]

Hi Fazza,

In which program (MS Query, Excel, Access) would you place the wild card eg LIKE '%whatever%' ?

I have used this (LIKE '%whatever%') in MS Query successfully already. In an Access Query, this will not work.

All I would like to do is create an Excel Pivot Table by directly using the Access Query with a LIKE '* whatever *' criteria without first importing the table into Excel.

Do you see a way to do this without MS Query?

Thanks for any help.
Kurt

 

[Fazza]

Yes, I know it doesn't work in Access: I assumed you were outside of Access and thought that it might simply be the wrong wildcard. It was not clear to me exactly what is being done.

I understand you have a query in Access. I don't know where you apply the criteria - there is some ambiguity in the question. I understand you don't want to use MS Query.

The ambiguity is in "create an Excel Pivot Table by directly using the Access Query with a LIKE '* whatever *' criteria "

Is that, (create the PT directly from the query) with a LIKE ...
or is it, create the PT directly from (the query with a LIKE ...)

I assume it is the former, cause the latter is simpler.

Without MS Query, you can use VBA. For example, use ADO to populate a recordset. If you need to filter, one way might be simply to filter the recordset. Then set the Excel pivotcache recordset to the same recordset, and complete the pivot table. There was a thread on Friday that had a similar idea. I'll have a look for a link.

regards, F