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.
GrahamSAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MINDSUPERBCommented:
You can't update an Excel spreadsheet from Access. You need to export your data through a Crosstab query into Excel.

So if you want to refresh your data in Excel through Access query, you might not get what you want.

Ed
0
GrahamSAAuthor Commented:
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.
0
MINDSUPERBCommented:
Ok.

See if the link below helps:
http://www.mrexcel.com/forum/showthread.php?t=330709

Ed
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

0
GrahamSAAuthor Commented:
Thanks Ed
I will look at all this and get back to you tomorrow...
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
GrahamSAAuthor Commented:
Hi Guys

Thanks for the input...
I have found a solution, totally by accident/

I created a normal access query off the crosstab and created a connection in excel to the new query.
This brings in a table the refreshes and looks exactly the same as the original query.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MINDSUPERBCommented:
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
0
GrahamSAAuthor Commented:
Thanks Friend

I wonder why Excel does not see the crosstab query its not hidden, I presume that spreadsheets dont quite work that way.
0
Jeffrey CoachmanMIS LiasonCommented:
Excel can only really see "Select" queries.
(Action queries are hidden as well)

0
GrahamSAAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.