none
No Data in Pivot Table Linked to Access Query

    Question

  • I inserted a Pivot Table in Excel 2007 using a query in Access 2007 as the external data source. Fields of the query show in Excel allowing me to build the pivot table, however no data comes through. I also noticed that if I follow the same process but use a table as the data source rather than a query the pivot table does populate with data. Also, confirmed in Access that the query does return data.

    I've done the same with Excel/Access 2003 with no issues.

    Thursday, July 15, 2010 7:52 PM

All replies

  • Hello Noel,

    There aren't any known issues like this that I could find, so let me ask a few more questions to put this problem in perspective.

    When importing the data from Excel, I assume you are using the built-in External Data functionality and after you select your MSAccess database you have a choice in the 'Select Table' dialog of choosing a saved query or a table. When you get to the point where the pivot table can be built, are you seeing the following?

    And are you seeing the checkboxes to the right of the empty pivot table? If so, does checking the fields you want at the top populate the pivot table with data?

    Will Buffington
    Microsoft Excel Support

     

    Wednesday, July 21, 2010 11:23 PM
    Moderator
  • Yes. It allows me to drag the fields which poplulate the pivot table but the data does not populate the pivot table. I have tried this with more than one database and I am getting the same results. The data populates the pivot table if I use a table but not if I use a query. I did just upgrade to 2007 from 2003. It worked fine with 2003. I imagine there is a simple solution. ;-)

    Thanks,

    Noel

     

    Thursday, July 22, 2010 9:30 PM
  • One other bit of wierdness. If I create a pivot table in Access based off the same query it works just fine- populates with the fields and data.
    • Marked as answer by Sally Tang Thursday, July 29, 2010 2:13 AM
    • Unmarked as answer by Sally Tang Thursday, July 29, 2010 2:14 AM
    Tuesday, July 27, 2010 3:10 PM
  • I apologize it's taken me a little while to get back with you on this. I've been searching for anything in the internal bug database related to this and while I've found a couple of occurrences which are similar but not exactly the same, they won't be of much help because they were Excel 2007 beta issues that were fixed prior to the release of Excel 2007.

    The first thing I want to suggest, of course, is make sure you have the latest service pack for Office 2007 installed. You can tell what SP you're at by clicking the Office button in Excel 2007 > click Excel Options > Resources. The version number and service pack level will appear at the bottom. Please let me know what version of Excel and MSO are shown.

    Thanks,

    Will

    Friday, July 30, 2010 11:08 PM
    Moderator
  • I am experiencing an identical problem.  I've created a workaround by creating a make-table query out of the query that cas the data I need but this is not a sustainable solution as it inflates the size of my Access DB.  Is there a permanent solution?
    Friday, August 13, 2010 6:23 PM
  • I am experiencing the same thing after upgrading to 2007 from 2003.  I am able to create pivot table with data from access tables but when I try to use access queries I only get the field names and no data.  I was able to do this with no problem in 2003 versions.  I am running Office 2007 SP2 MSO 12.0.6535.5002.
    Friday, August 20, 2010 3:57 PM
  • Hi Noel,

    I'm having this same exact problem, i.e., Excel pivot table links to Access query which queries a linked table that resides on SQL Server and no data comes through. If I change the Access query to get the data from a "local" table the pivot table displays the data.

    Did you manage to solve this issue? I'm using Office 2007 and SQL Server 2000. Please let me know if you have any suggestions at all as this is causing one of the analysts a lot of grief!

    Thanks!

     

     

    Wednesday, September 08, 2010 9:19 AM
  • Has there been no update on this?  My office just "upgraded" to 2007 and we tested an Excel pivot table linked to an Access query and it was worse than giving no data, it gave wrong data. 

    Background: the linked pivot table already existed in an old version office (both Excel and Access) and worked perfectly.  We loaded the file in Excel 2007, refreshed data, and found 1/3 of it missing (one of the data fields is Report, Med, or Ind, all of which occur in the data, but the pivot table dropped the "Ind" data items).  Thinking that it was a problem with reverse compatibility, we converted the Access database to 2007 and created a new Excel workbook in Excel 2007 with a pivot table linking to the query.  Now, we still can't see the records with "Ind" in the above mentioned field.  Additionally, the query takes just the left 10 characters for another field, but that field is showing all original characters (would you say it's keeping too much info?) so the grouping wouldn't be working correctly even if all the data were present.

     

    Please help, this error will make MS Office nearly unusable.

    Friday, January 14, 2011 2:33 AM
  • Hi

    In XL2007 and 2010, IND is a column, hence XL will not allow you to use a reference of Ind.
    Change it to _Ind or something similar, and all should work OK.

    There are a few "gotcha's" with 3 letter words with column references that now continue from IW to XFD



    Regards
    Roger Govier

    "SloCal" wrote in message news:84713130-3f1b-403d-90e9-b1a4d69a2cf5@communitybridge.codeplex.com...

    Has there been no update on this?  My office just "upgraded" to 2007 and we tested an Excel pivot table linked to an Access query and it was worse than giving no data, it gave wrong data.

    Background: the linked pivot table already existed in an old version office (both Excel and Access) and worked perfectly.  We loaded the file in Excel 2007, refreshed data, and found 1/3 of it missing (one of the data fields is Report, Med, or Ind, all of which occur in the data, but the pivot table dropped the "Ind" data items).  Thinking that it was a problem with reverse compatibility, we converted the Access database to 2007 and created a new Excel workbook in Excel 2007 with a pivot table linking to the query.  Now, we still can't see the records with "Ind" in the above mentioned field.  Additionally, the query takes just the left 10 characters for another field, but that field is showing all original characters (would you say it's keeping too much info?) so the grouping wouldn't be working correctly even if all the data were present.



    Please help, this error will make MS Office nearly unusable.


    ________ Information from ESET Smart Security, version of virus signature database 5785 (20110113) ________

    The message was checked by ESET Smart Security.

    http://www.eset.com

    ________ Information from ESET Smart Security, version of virus signature database 5785 (20110113) ________

    The message was checked by ESET Smart Security.

    http://www.eset.com


    Roger Govier Microsoft Excel MVP
    Friday, January 14, 2011 9:48 AM
  • Thanks for the suggestion Robert, but I tried changing "Ind" to another, longer, name with no success.

     

    As it turns out, after we spent more time digging into the issues, we found that both problems  were coming from misinterpretations of wildcards.  In office 2003, "*" was taken as a wildcard in SQL Like statements.  Documentation says that "*" or "%" will work, but neither worked properly.  When we use 'Like "*C*"' Access recognizes correctly and returns the correct data, but the pivot table accessing that query will read that as always false.  But when we switch to 'Like "%C%"', Access evaluates as false but the Excel Pivot Table will then actually have all the correct data!

     

    If you want to look up details on this, check ANSI-89 vs ANSI-92 standards and note that Office 2007 is supposed to accept both.

     

    Any ideas?

    Saturday, January 15, 2011 7:06 AM
  • Found a possible solution:

    If the empty table is being caused by the use of "*" as a wildcard (as was the case with us), then you can change access to run on ANSI-92 syntax (Access Options - Object Designers - check the box under SQL server compatible syntax (ANSI-92) for This Database).  Then go through your queries and change all "*" wildcards to "%".  Now the queries will run properly in Access and the output will be visible in Excel.

    This does lead to the side question: why would Excel be over-riding the query language interpretations of Access?

     

    Anyway, hope this helps others who have been having similar issues.

    • Proposed as answer by SloCal Friday, January 21, 2011 6:47 PM
    Friday, January 21, 2011 6:46 PM
  • THANK YOU to one of the replies ... we had a query that had the wildcard charcters in it, by removing the "*" we got the data.   We actually needed to do a workaround in our database table to elimnate the need for the wild card in the query.   MICROSOFT Folks, this should be treated as a BUG ... and fixed if possible.
    • Edited by JE_55 Thursday, September 08, 2011 11:34 PM
    Thursday, September 08, 2011 11:05 PM
  • Thanks for these replies, this helped me fixing my Access query as well... 

     

    While fixing the issue I faced, I find another solution to the wildcard problem... In my case I was using wildcard "*" to query for "Begins with" and when I changed my query to identify first 4 characters from left using "Left" functionality it worked absolutely fine.

    Hope this may be of help to different users while doing their query.

     

    Tuesday, October 18, 2011 11:29 AM
  • Just for the record, I was suffering from the same problem with Access 2010 (MS Professional version 2010 Plus, Access version 14.0.6112.5000 (32 bit)) . Finally I removed the wildcard and used Left function instead.
    Sunday, March 04, 2012 10:16 AM
  • FYI I was receiving 'No data in field' prompts when using a 2007 Excel query with a datalink to Access as well.

    Removed the Like function from the query criterion - suddenly works like a charm...

    Wednesday, August 08, 2012 12:23 PM
  • check this link this may help you out

    http://www.myonlinetraininghub.com/create-a-pivottable-direct-from-access

    Wednesday, August 08, 2012 5:19 PM
  • SOLUTION:  If anyone has this same issue it is due to using wildcards in your query.  One way around this is to Get External Data  From Other Source under the Data tab on Excel 2013.  Search for your database then query and the Data should come through exactly as it has in your query.

    Hope this helps someone:)

    Tuesday, July 30, 2013 3:34 PM