Getting ACCESS query into Excel Pivot Table 1

[jpl458]

Hope this is the right forum, if not tell me where to go.
I've forgotten how to do this. I have a query in an ACCESS database and I want to use that query in an EXCEL Pivot table. I have tried the following Data>Get External Data>From ACCESS>Navigate to the folder>Select the DB> but can't get to a point where I can select the query. When I insert the above into a pivot table there is no data, obviously.

Windows 7, Office 10

Any help would be appreciated

Thanks

jpl

 

[SkipVought]

hi,

So exactly what happens after you select your database?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpl458]

The pivot table displays the query labels but says that there is no data. When I select the ACCESS DB I can only get to tables, not to queries.

 

[SkipVought]

In the Add Tables window, click the OPTIONS button and CHECK ALL BOXES.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpl458]

Never see an Add Tables Window. Here is what i am doing. I have this access db with one table and one query. The query name is Faults. The table is a SQL Server table. The query runs as it should.

In Excel I Insert>Pivot Table>Use an external data source>Choose Connection>brouse to FaultsDB but wqhen it Lists the FaultsDB there is no option to select tables or queries.

Thanks for the response

jpl

 

[SkipVought]

Rather than that, get out of the PT and go to a new sheet, use Data > Get external data > From access... And IMPORT the data into the sheet. THEN pivot that data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpl458]

Slowly it comes back as to how I did it so easily before.

Go to Data>from ACCESS>Browse to the folder>click on the DB> list of tables and queries appears>select what I want and Voila (Do I want it in a Table or report)--except for the fact that this works for other queris, but not the faults query for some reason.

I recreate everything and see where it goes.

Thanks again for the help.

jpl

 

[jpl458]

Found the answer, and it's not pretty. It seems there is a problem with wildcards when using data from an ACCESS Query to a Pivot table in Excel. Read below from the Web:

As it turns out, after we spent more time digging into the issues, we found that both problems were coming from misinterpretations of wildcards. In office 2003, "*" was taken as a wildcard in SQL Like statements. Documentation says that "*" or "%" will work, but neither worked properly. When we use 'Like "*C*"' Access recognizes correctly and returns the correct data, but the pivot table accessing that query will read that as always false. But when we switch to 'Like "%C%"', Access evaluates as false but the Excel Pivot Table will then actually have all the correct data!


I tried this fix (using Windows 7 and Office 10) and it works. I had a column that was constrained with Like "M*" and when i changed it to Like :"%M%" it worked as it should.

Hope this helps someone else.

jpl