Can crosstab sql show more details in column? (1 Viewer)

[leom808]

Hi all!
I'm struggling in modify current crosstab sql statement in order to provide more I/O details on the query result. Following are the current detail:
-----------------------------------------------
Table: TxnMst
Fields:
TxnDat Date/Time
ItmNum Text (30)
Qty Number (Long Integer)
Table data:
TxnDat. ItmNum Qty
01/1/13 AAAA.. 25
02/1/13 AAAA.. -5
01/2/13 AAAA... 2
02/3/13 AAAA.. -1
01/3/13 AAAA... 1
01/1/13 BBBB.. 23
14/2/13 BBBB.. -1
15/2/13 BBBB... 1
16/2/13 BBBB.. -1
03/1/13 CCCC... 5
08/2/13 CCCC.. -2
-----------------------------------------------------------------------
Crosstab SQL:
TRANSFORM Sum(TxnMst.[Qty]) AS QtyOfSum
SELECT TxnMst.[ItmNum], Sum(TxnMst.[Qty]) AS [Balance]
FROM TxnMst
GROUP BY TxnMst.[ItmNum]
PIVOT Format([TxnDat],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Query Result:
ItmNum Balance Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
AAAA....... 22. 20.. 2.. 0
BBBB....... 22. 23. -1
CCCC........ 3.. 5. -2
.."
.."
.."
-----------------------------------------------------------------------
As the above simple SQL statement can only show the monthly balance,
can we generate following report format base on current table data?

.............................JAN.......... Feb.............. Dec
........................----------. -----------... -----------
ItemNum Balance In Out Bal.. In Out Bal...... In Out Bal


Assume qty value in the table has special meaning: +ve value=In qty, -ve value=Out qty

Thanks in advance!!!

 

[leom808]

already find a solution how to create the query that I want, but still looking for a solution how to create a "B/F column" and how to accumulate the previous month's qty into next month...

i.e. ItemNum Balance (B/F_qty) Jan(In)_(Out)_(Bal) Feb(In)_(Out)_(Bal).....

where the value of Feb..(Bal) should be = Jan's (Bal)+Feb(In)-Feb(Out)

Can anyone help?