Adding a pivot table in Excel 2007 from a QUERY in Access 2007

[Neurogenesis]

What am I missing here? In Excel 2003 I was able to create a pivot
from a query in a Access 2003 database.

Now when I go to create the pivot in Excel 2007 I only get the Access
table to show up, not the queries.


Process in Excel 2003:

Data>PivotTable and PivotChart Wizard> External Data Source>Get
Data>MS Access Database>(Select Database)>(Select Access Query and
move from "Available tables and columns" to "Columns in your
query">Next>Next>Next>Finish

Process in Excel 2007:
Insert Tab> PivotTable>Use an external data source>Choose
Connection>Browse for more>(Select Database)>Open
....it is at this point in Excel that I cannot see the queries that I
generated in Access.

Thanks in advance for your help.

 

[Debra Dalgleish]

In Excel, on the Data tab, click From Access
Select your database, click Open
Select the query, click OK
Select PivotTable report, click OK

 

[Neurogenesis]

In Excel, on the Data tab, click From Access
Select your database, click Open
Select the query, click OK
Select PivotTable report, click OK











--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text -

- Show quoted text -

This still only shows the tables though, it doesnt show queries.

Process I can only follow (as it allows):

From Data Tab>From Access> Chose database>Click open>(this is where it

blows up, it then asks me to chose what table I want to use, none of
the queries are listed)

Thanks!

 

[Debra Dalgleish]

The queries show up for me, except for Union queries.
Is this the same database that you used in Excel 2003?

 

[Neurogenesis]

The queries show up for me, except for Union queries.
Is this the same database that you used in Excel 2003?









--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text -

- Show quoted text -

I've tried both DB created on 2003 and 2007, same issue on both. Thing
is, I wrote all of the queries (which half of them are UNIONs, in the
SQL view...I didn't use the GUI)

 

[ah90]

Hi,

I'm having a similar problem, in that my pivot table doesn't return any values even though I know the query does contain values.

It is to do with Excel 2007 working in ANSI-92 format. Any queries that contain "*" wildcards do not work in Excel 2007! You can convert your Access 2007 database to ANSI-92, but it requires changing your "*" wildcard to "%" for everything.
Not practical for historic work you may have done.

 

[ah90]

Ha, only 4 years late with the reply, just noticed, sorry!