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

  • Thread starter Thread starter Neurogenesis
  • Start date Start date
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.
 
In Excel, on the Data tab, click From Access
Select your database, click Open
Select the query, click OK
Select PivotTable report, click OK
 
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 -
Click to expand...

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
Click to expand...
blows up, it then asks me to chose what table I want to use, none of
the queries are listed)

Thanks!
 
The queries show up for me, except for Union queries.
Is this the same database that you used in Excel 2003?
 
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 -
Click to expand...


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)
 
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.
 
Back
Top