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