Link to home
Start Free TrialLog in
Avatar of HOTWATT
HOTWATTFlag for United States of America

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
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HOTWATT

ASKER

That was the problem. I knew it had to be something simple. Thanks for the help!
You are welcome!

/gustav