Crosstab Query refresh
Hi Experts
Greetings from SA, hope all is well!
OK I have a crosstab query in access that suits my need perfectly however the report that goes to senior management is an excel file that is fed by quite a few queries and tables done in access.
Is there a way to add the crosstab query to excel and have it refresh with all the other info.
When I go the "Data, From Access" route excel cant see the crosstab query, it sees everything else in the access file.
Greetings from SA, hope all is well!
OK I have a crosstab query in access that suits my need perfectly however the report that goes to senior management is an excel file that is fed by quite a few queries and tables done in access.
Is there a way to add the crosstab query to excel and have it refresh with all the other info.
When I go the "Data, From Access" route excel cant see the crosstab query, it sees everything else in the access file.
ASKER
Hi
The refresh is done in excel not access which works 100% I need to add the crosstab info to the workbook and have it also be refreshable.
if I export the crosstab out off access into excel it has no ability to refresh.
The refresh is done in excel not access which works 100% I need to add the crosstab info to the workbook and have it also be refreshable.
if I export the crosstab out off access into excel it has no ability to refresh.
GrahamSA,
Try to check the query you mentioned in Access if its property not set to hidden. Any object hidden in the design view are greyed out.
Sincerely,
Ed
Try to check the query you mentioned in Access if its property not set to hidden. Any object hidden in the design view are greyed out.
Sincerely,
Ed
ASKER
Thanks Ed
I will look at all this and get back to you tomorrow...
I will look at all this and get back to you tomorrow...
In Excel the equivalent object to a crosstab query is a "Pivot table"
So it might be simpler to just create a Pivot in Excel from from the Access data.
The Pivot table will refresh each time it is opened, or you can refresh it manually by selecting Refresh from the Data section of the Options tab
So it might be simpler to just create a Pivot in Excel from from the Access data.
The Pivot table will refresh each time it is opened, or you can refresh it manually by selecting Refresh from the Data section of the Options tab
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Good that you figure it out.
I was thinking that the query you mentioned is hidden. Any hidden tables and queries in access will not display in Excel when you do go to Data -> From Access.
Sincerely,
Ed
I was thinking that the query you mentioned is hidden. Any hidden tables and queries in access will not display in Excel when you do go to Data -> From Access.
Sincerely,
Ed
ASKER
Thanks Friend
I wonder why Excel does not see the crosstab query its not hidden, I presume that spreadsheets dont quite work that way.
I wonder why Excel does not see the crosstab query its not hidden, I presume that spreadsheets dont quite work that way.
Excel can only really see "Select" queries.
(Action queries are hidden as well)
(Action queries are hidden as well)
ASKER
I found the solution completely by accident, the solution works perfectly.
I posted the question because i couldn't seem to find out how to solve the problem
I posted the question because i couldn't seem to find out how to solve the problem
So if you want to refresh your data in Excel through Access query, you might not get what you want.
Ed