Excel Pivot Table not bringing back any data from Access Database Query

[Luke]

I am trying to use an Access Query that is pulling from a local table and a linked table from another database to create an Excel Pivot table. I am not getting any data in my Pivot table. The Access query is actually a query built on a intermediate query that uses a table linked from another database + a local table. The query runs correctly in Access, but when I pull it into a Pivot table it brings back no information. The headings from the query pull into the Pivot correctly, just no data.


Is it not possible to run an Excel Pivot table based on a Access table linked from another database?

 

[dan_l]

------

I am trying to use an Access Query that is pulling from a local table and a linked table from another database to create an Excel Pivot table. I am not getting any data in my Pivot table. The Access query is actually a query built on a intermediate query that uses a table linked from another database + a local table.

-------


lol


Are you using MS Query for this? Or is this an ado thing? Is the pivot tied directly into the query or does it drop to a staging area and refresh into the pivot?

 

[Luke]

The pivot is tied directly into the query.


I actually think it is probably a network permissions thing. The Database that contains the query is using a linked Data table from another users folder to pull the sales data. The problem is that neither of these folders are my network folder, I have just been granted up to modify permission. When I go to link to anything in the Access database with my Excel Pivot, none of the linked tables show up at all as a choice. (they are all linked to a database outside of this users folder)

 

[dan_l]

Quirky. Weird that you can run it in access but not in excel. Is this one of those 'Access projects that should never have been" kind of things?

 

[Luke]

I was trying to solve a info request problem for a department that is comfortable with Excel but not the ins and outs of Access. I came up with a workaround that allows them to run 1 make table query each month in the Access Database so that it is a local table and then all they have to do is open the pivot and hit refresh.


I was trying to do it so that they didn't have to touch the Access database at all.

 

[akashbest@gmail.com]

I dont why but if you remove all wildcards (i.e Like, *) from your access query and re-create your pivots. It should work.