HOTWATT
asked on
Question about cross tab query
I created a cross tab query that is working the way I wanted. The only issue is for every new record it creates another line. I am trying to list one part # across with each months qty. When I run the query if there is a qty in each month it will create a new line so there are multiple of the same part #. I attached a photo to help explain better than I can. Any idea how to show one line with the qty for each month?
End goal is to look like this
01/17 02/17 03/17
02A0009-03 132 0 5
SQL
TRANSFORM Sum(PK1_TX.TX_QTY) AS SumOfTX_QTY
SELECT PK1_IM.IM_KEY
FROM PK1_IM INNER JOIN PK1_TX ON PK1_IM.IM_KEY = PK1_TX.TX_IMKEY
WHERE (((PK1_IM.IM_KEY) Like "02A*" Or (PK1_IM.IM_KEY) Like "02B*" Or (PK1_IM.IM_KEY) Like "07A*") AND ((PK1_TX.TX_DATE) Between #12/14/2016# And #12/14/2017#) AND ((PK1_TX.TX_TYPE)="I"))
GROUP BY PK1_IM.IM_KEY, PK1_IM.IM_ON_HAND, PK1_TX.TX_DATE, PK1_IM.IM_UNIT_S, PK1_IM.IM_UNIT_I, PK1_IM.IM_UNIT_R, PK1_TX.TX_TYPE, PK1_TX.TX_MOSJOB, PK1_TX.TX_MOSLOT
PIVOT Format([TX_DATE],"mm\/yyyy ");
example-of-cross-tab-query.PNG
End goal is to look like this
01/17 02/17 03/17
02A0009-03 132 0 5
SQL
TRANSFORM Sum(PK1_TX.TX_QTY) AS SumOfTX_QTY
SELECT PK1_IM.IM_KEY
FROM PK1_IM INNER JOIN PK1_TX ON PK1_IM.IM_KEY = PK1_TX.TX_IMKEY
WHERE (((PK1_IM.IM_KEY) Like "02A*" Or (PK1_IM.IM_KEY) Like "02B*" Or (PK1_IM.IM_KEY) Like "07A*") AND ((PK1_TX.TX_DATE) Between #12/14/2016# And #12/14/2017#) AND ((PK1_TX.TX_TYPE)="I"))
GROUP BY PK1_IM.IM_KEY, PK1_IM.IM_ON_HAND, PK1_TX.TX_DATE, PK1_IM.IM_UNIT_S, PK1_IM.IM_UNIT_I, PK1_IM.IM_UNIT_R, PK1_TX.TX_TYPE, PK1_TX.TX_MOSJOB, PK1_TX.TX_MOSLOT
PIVOT Format([TX_DATE],"mm\/yyyy
example-of-cross-tab-query.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You are welcome!
/gustav
/gustav
ASKER