CODEWITHSUNDEEP
sqlms-accessms-access-2007
Gelo Reyes
Gelo Reyes

Reputation: 59

SQL Query PIVOT to MS Access SQL Query

I have this query on SQL Server

    ;WITH tmpTbl AS
    (SELECT Kit_Tbl.Kit_Number
           ,Kit_Tbl.Kit_Refrigerant
           ,CompType_Tbl.Component_Type
           ,Comp_List_Tbl.Component_Num
            FROM Kit_Tbl
    INNER JOIN Kit_Library
    ON Kit_Library.Kit_Number = Kit_Tbl.Kit_Number
    INNER JOIN CompType_Tbl
    ON CompType_Tbl.Component_Type = Kit_Library.Component_Type
    INNER JOIN Comp_List_Tbl
    ON Comp_List_Tbl.Component_Type = CompType_Tbl.Component_Type)

    select Kit_Number
         , Kit_Refrigerant
         , [Compressor]
         , [Condensing Unit]
    from
    (
      select Kit_Number, Component_Type, Component_Num, Kit_Refrigerant
      from tmpTbl
    ) d
    pivot
    (
      max(Component_Num)
      for Component_Type in ([Compressor], [Condensing Unit])
    ) piv;

I tried converting it to MS Access query but I encountered Syntax Error on Transform Statement:

    TRANSFORM MAX(Comp_List_Tbl.Component_Num) AS Comp_Num
    SELECT Kit_Tbl.Kit_Number,
    CompType_Tbl.Component_Type,MAX(Comp_List_Tbl.Component_Num)
    FROM Comp_List_Tbl INNER JOIN (Kit_Tbl INNER JOIN (Kit_Library INNER JOIN
    CompType_Tbl ON Kit_Library.Component_Type = CompType_Tbl.Component_Type) ON 
    Kit_Tbl.Kit_Number = Kit_Library.Kit_Number) ON (CompType_Tbl.Component_Type = 
    Comp_List_Tbl.Component_Type);
    GROUP BY Kit_Tbl.Kit_Number 
    PIVOT IN CompType_Tbl.Component_Type

Can anyone help me with this?

Upvotes: 0

Views: 84

Answers (1)

parakmiakos
parakmiakos

Reputation: 3020

In your last line : 

PIVOT CompType_Tbl.Component_Type

No IN is required.

Upvotes: 1

Related Questions