Is it not possible to link Excel to a crosstab query?

[Johnny C]

Hi

I'm setting up an Excel file to report on Access data. At the moment it's copy&paste but I'm trying to set up a connection to the crosstab table in Access. I can see the database, tables and a few queries in thge connection wizard. I can't see maketable queries which is what I'd expect, but I can't see the crosstab I'm looking for either. Is it not possible?

 

[namliam]

Why link to a crosstab? Just make it a normal query and use a pivot in excel, so much more powerfull

 

[Johnny C]

Just because it's too big for Excel, it's a database with 5m rows in a table created for this one query. I could do it using MS query but it's too slow. Copy & paste it will have to be alas.

 

[Johnny C]

Plus, the data is picked up by a few charts. The number of rows and columns is potentially dynamic and won't work with a pivot table. With an access data pull I could use table range names.

 

[namliam]

You can link the pivot in excel based on the select query in Access that shouldnt be bothered if it has 5m records.
Also you can offcourse already do the totalling in a "simple" group by query so that the pivot isnt actually doing much counting but instead is just changing the display of things.
Which should reduce the number of rows significantly