PIVOT in Microsoft Query

[sue0912]

Good morning!

I am getting syntax errors in microsoft query code when trying to use PIVOT

TRANSFORM Sum(Temp_Parts.Ext_Amnt) AS SumOfExt_Amnt
SELECT Temp_Parts.LastOfDivision, Temp_Parts.Customer
FROM Temp_Parts
GROUP BY Temp_Parts.LastOfDivision, Temp_Parts.Customer
PIVOT Temp_Parts.Prod_Line

Error I receive
Incorrect Syntax near 'Temp_Parts'.
Incorrect Syntax near 'PIVOT'.
Statements could not be preparted.

Any suggestions on what I am doing incorrectly?

Thanks
Sue

 

[SkipVought]

Hi,

YOU have to debug this.

Does this run?

SELECT
  LastOfDivision
, Customer
, Prod_Line
, Sum(Ext_Amnt) AS SumOfExt_Amnt

FROM
  Temp_Parts

GROUP BY
  LastOfDivision
, Customer
, Prod_Line

That's what I would do FIRST.

Skip,

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

 

[sue0912]

Yes it runs.

 

[dhookom]

I wasn't aware that MS Query supported PIVOT like an Access crosstab query. If your application is Excel, have you considered querying the records and then creating a pivot table based on the MS Query?

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Duane, Yes, MS Query supports Transform, Pivot. However, your suggestion regarding the PivotTable is a good alternative.

Skip,

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