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


N

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.
 
Ad

Advertisements

D

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
 
N

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!
 
D

Debra Dalgleish

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

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)
 
Joined
May 12, 2011
Messages
2
Reaction score
0
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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top