I need to build an Item usage report that summarizes inventory transactions by month and by item #. Crosstab seems to be the answer but i have 2 issues:
#1: require last 24 months of data but current month cannot be included in the row Grand Total; current month must appear in a column on its own;
So far i've got
Rows = Item#, Columns = Month, Summarized Field = Qty. All these records coming from table Inventory_Transaction.
Date query: Transaction date in (dateserial(year(currentdate)-2,month(currentdate)+1,1)) to today
#2: For the same item#'s need to display in that same report a "Qty on Hand" column which is calculated field found in another table "Items". Items.Qty_On_Hand
Report looks something like:

Thanks