Trying to build an EXCEL Pivot Repo 1

[jpl458]

Trying to build an EXCEL Pivot Report by getting the data from an access query. When ever I constrain the query with a Like clause, or an expression the data the goes to the Pivot Report is empty. If I remove the constraints then all the data comes across. I can find no way to constrain the fields once they're in the pivot table. There are about 750,000 rows and in the description field is the term Fault in some of the records, and there are too many possibiloites in the description of select in the pivot report. I just want to ke the fault recors from ACCESS.

Run into this before.

Any help would be appreciated.

Thanks in advance

jpl

 

[SkipVought]

hi,

What version of Excel?

How are you getting the data from Access?

Skip,

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

 

[jpl458]

Using Office 2010. If I don't constrain the query it runs and displayes in EXCEL, If I enter a Like "Fault*" the query runs fine in ACCESS, but when I bring into EXCEL via Data-From ACCESS the pivot Report appears but there is no data in the fields. I can probably solve this by running the first query than run another query with the first as input, but is not very cool.

Thanks

JPL

 

[jpl458]

BTW Queries with no constraints come to excel just fine.

jpl

 

[SkipVought]

Pls post you SQL

Skip,

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

 

[jpl458]

Here is the sql
SELECT dbo_Transaction_Table.Location_Id, dbo_Transaction_Table.Description, IIf(Left([dbo_Transaction_Table]![Description],3)="Man",1) AS Fault, Year([dbo_Transaction_Table]![Date_of_Transaction]) AS [Year], Month([dbo_Transaction_Table]![Date_of_Transaction]) AS [Month], Day([dbo_Transaction_Table]![Date_of_Transaction]) AS [Day], Hour([dbo_Transaction_Table]![Time_of_Transaction]) AS [Hour]
FROM dbo_Transaction_Table
WHERE (((dbo_Transaction_Table.Description) Like "man*"))
GROUP BY dbo_Transaction_Table.Location_Id, dbo_Transaction_Table.Description, IIf(Left([dbo_Transaction_Table]![Description],3)="Man",1), Year([dbo_Transaction_Table]![Date_of_Transaction]), Month([dbo_Transaction_Table]![Date_of_Transaction]), Day([dbo_Transaction_Table]![Date_of_Transaction]), Hour([dbo_Transaction_Table]![Time_of_Transaction])
ORDER BY dbo_Transaction_Table.Location_Id;

jpl

 

[SkipVought]

In what application do you execute this SQL?

Skip,

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

 

[jpl458]

The query is an an access database. In excel I get External data, From ACCESS and select this query from the access application where it resides. It runs in excel if I take out the Like statements, but won't with them in. I can't really say where it executes. There is a progress bar across the bottom of Excel as the query runs. I hope it's not that some sqL works ACCESS but not EXCEL.

jpl

 

[jpl458]

Just ran a buch of simple queries made in access and brought into excel. Anytime A like clause is in the ACCESS query no data comes into Excel even though a pivot report in opened. There is no data in any of the fields. Feels like an MS gotcha!

jpl

 

[SkipVought]

Rather than run an Access query, code the identical query in the MS Query QBE Grid.

Skip,

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

 

[jpl458]

Here is a snippit from the web:

In Excel, the Like operator is used with ? and * to designate one or many missing characters, respectively. Someone recently asked me about using the Like operator in MSQuery. Since I didn’t know off the top of my head, I went to the help file. If there’s anything in the help file about this, I couldn’t find it.

In MSQuery, Like works just how you’d expect it to, that is if you’ve used Like in some other program. The difference is that MSQuery’s Like appears to have only one wildcard character, namely the percent sign (%). It stands in for multiple characters, similar to the asterisks (*) in Excel. The criterion


Like ‘KU%’

will return all the records that start with”KU”. Like appears to be case insensitive. That is, the above criterion would also return records that start with “ku”, “Ku”, and “kU”.
That is the probelm, the two MS APs hae different rules for each version of SQL. I use access since i know it, and the MS SQL is cumbersome and I think more robust.

I will try entering the SQL directly into EXCEL

Thanks
jpl