Pivot Table in MS Query 1

[shelby55]

Hi

I am trying to create a pivot table in MS Query using SQL for an Excel worksheet I have.

What I've been doing to assist with SQL code is write in an MS-Access query and then copy the SQL code from there to MS Query.

However, I can't get it to work for a pivot table. The MS Access formula indicates:

TRANSFORM Sum([09_10].[Unique Individual]) AS [SumOfUnique Individual] SELECT [09_10].[Reason for Discharge], Sum([09_10].[Unique Individual]) AS [Total Of Unique Individual] FROM 09_10 WHERE ((Not ([09_10].[Reason for Discharge]) Is Null)) GROUP BY [09_10].[Reason for Discharge] PIVOT Format([Discharge Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

However, I keep getting errors on the TRANSFORM statement and it doesn't like the format "mmm" statement.

Any and all assistance greatly appreciated!

Thanks.

Shelby

 

[shelby55]

Hi

I edited the code to:
TRANSFORM Sum(`'09_10$'`.`Unique Individual`) AS [SumOfUnique Individual]
SELECT `'09_10$'`.`Reason for Discharge`, Sum(`'09_10$'`.`Unique Individual`) AS [Total Of Unique Individual] FROM `'09_10$'` `'09_10$'` WHERE ((Not (`'09_10$'`.`Reason for Discharge`) Is Null))
GROUP BY `'09_10$'`.`Reason for Discharge`
PIVOT Format(`'09_10$'`.`Discharge Date`,"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

And I get an error message of "too few parameters. Expected 1."

Shelby

 

[SkipVought]

Shelby,

Aggregation is in Transform ONLY. You seem to have Summed [Unique Individual] twice.

Also, from your previous post, I suspect you "dates" may not be READ DATES. Can you CHANGE the display value of the "date" to a NUMBER in Excel when your change the format to GENERAL? If you have REAL dates, the posted code will probably work.

    sSQL = "TRANSFORM "
    sSQL = sSQL & "   Sum(`Unique Individual`) AS 'SumOfUnique Individual'"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "SELECT "
    sSQL = sSQL & "  `Reason for Discharge`"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM "
    sSQL = sSQL & "  `'09_10$'`"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE `Reason for Discharge` Is Not Null"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "GROUP BY"
    sSQL = sSQL & "  `Reason for Discharge`"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "PIVOT"
    sSQL = sSQL & "  Format(`Discharge Date`,'mmm')"

Also, you can perform a PivotTable, using MS Query directly, withour copying code from MS Access, which often can be DIFFERENT.

Skip,

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

 

[shelby55]

Hi Skip

Thanks so much. So for the provided code, this is in "MS Query" SQL window or am I to be placing this elsewhere?

Thanks.

Shelby

 

[SkipVought]

Sorry,
[tt]
TRANSFORM Sum(`Unique Individual`) AS 'SumOfUnique Individual'
SELECT `Reason for Discharge`
FROM `'09_10$'`
WHERE `Reason for Discharge` Is Not Null
GROUP BY `Reason for Discharge`
PIVOT Format(`Discharge Date`,'mmm')
[/tt]
Note that your Discharge Dates will not sort as expected. you will have to sort columns after the resultset is returned.

Skip,

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

 

[shelby55]

Hi Skip

You rule - worked great!! When you say to sort columns "after result set is returned" you mean returned back to Excel, correct? I try to shift in the MS Query and it doesn't work.

Also, there will be data continuously entered for this worksheet so columns that aren't present now because not data or changes to the columns now, does that mean the month headings will always be "out of order"?

Thanks.

 

[SkipVought]

Your months are TEXT. Consequently Apr & Dec sort before Jan, for instance.

You can sort columns using a macro. Go to

http://www.microsoft.com

and search on AfterRefresh. Then use this event to trigger sorting the columns into actual month sequence.

Alternatively, format the date like this...
[tt]
PIVOT Format(`Discharge Date`,'yyyy/mmdd')
[/tt]


Skip,

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

 

[shelby55]

Hi

Thanks so much, Skip. Your amazing expertise has guided me through this whole project (which we both know should have been in Access but the people using it refused!!).

You are awesome!

 

[shelby55]

Hi

Sorry Skip but I have one more question: is there any way to have a totals column PLUS the totals per month column (like in Access)?

Thanks!

Shelby

 

[shelby55]

Hi

Actually, based on the SQL from a cross-tab MS-Access Query I successfully added the totals to the select statement:

TRANSFORM Sum(`Unique Individual`) AS 'SumOfUnique Individual'
SELECT `Reason for Discharge`, Sum(`Unique Individual`)
FROM `'09_10$'`
WHERE `Discharge Date` Is Not Null
GROUP BY `Reason for Discharge`
PIVOT Format(`Discharge Date`,'yyyy-mm')

However, this new column shows up as "Expr1003" even though I originally added the AS statement to it. Anyway to change this? Then I promise this will be my last query about this project!!

Shelby

 

[SkipVought]

Please post some sample source data for me to use.

Skip,

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

 

[SkipVought]

As I stated earlier, the aggregation belongs in the TRANSFORM statment and NOT the SELECT statement. The alias does nothing for the query.
[tt]
TRANSFORM Sum(`Unique Individual`)
SELECT `Reason for Discharge`
FROM `'09_10$'`
WHERE `Discharge Date` Is Not Null
GROUP BY `Reason for Discharge`
PIVOT Format(`Discharge Date`,'yyyy-mm')
[/tt]
Actually, since you are using a TRANSFORM, you don't need the PivotTable Wizard.

You could, however, use the PivotTable Wizard with THIS SQL code...
[tt]
SELECT `Reason for Discharge`, `Discharge Date`, Sum(`Unique Individual`) AS 'SumOfUnique Individual'
FROM `'09_10$'`
WHERE `Discharge Date` Is Not Null
GROUP BY `Reason for Discharge`
[/tt]
or even better...
[tt]
SELECT `Reason for Discharge`, `Discharge Date`, `Unique Individual`
FROM `'09_10$'`
WHERE `Discharge Date` Is Not Null
[/tt]
and have the PT do the summing AND the pivot.



Skip,

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

 

[shelby55]

Hi Skip

Now you've confused me....the Pivot Wizard won't let me query from the table itself using MSQuery.

Where does the SQL code you have indicated go?

 

[SkipVought]

Data > PivotTable External Data Source...

Skip,

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

 

[shelby55]

Hi

I tried to use that method earlier but I get an error when selecting my Excel worksheet as the data source:"unrecogized database format".