?
Solved

Crosstab Query refresh

Posted on 2011-10-26
11
Medium Priority
?
458 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:GrahamSA
  • 5
  • 4
  • 2
11 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 37036257
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
 

Author Comment

by:GrahamSA
ID: 37036285
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
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 37036345
Ok.

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

Ed
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 37036387
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
 

Author Comment

by:GrahamSA
ID: 37036514
Thanks Ed
I will look at all this and get back to you tomorrow...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37038322
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
 

Accepted Solution

by:
GrahamSA earned 0 total points
ID: 37039412
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
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 37039973
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
 

Author Comment

by:GrahamSA
ID: 37040026
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37040536
Excel can only really see "Select" queries.
(Action queries are hidden as well)

0
 

Author Closing Comment

by:GrahamSA
ID: 37087288
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question