Using Inventory transactions to create a history of inventory levels
Posted on 2004-03-30
I have a table (Table 1) of Inventory Transactions containing fields :
Inventory Name; SubInventory Code; Item Number; Transaction Date; Transaction Quantity.
I have created a query to show stock levels following a transaction by using
Stock Level: DSum("[Table1]![Transaction Quantity]","[Table1]","[Table1]![Inventory Name] ='" & [Inventory Name] & "' AND [Table1]![SubInventory Code] ='" & [SubInventory Code] & "' AND [Table1]![Item Number] =" & [Item Number] & "’ AND ([Table1]![Transaction Date]) <= #" & Format([Transaction Date]," mm/dd/yyyy") & "# ")
I want to graph a history of stock levels vs time.
I used a crosstab to generate the source data for the graph.
BUT, If there are no transactions on a given day for a given item in a given inventory, there are no records in Table 1 and so the crosstab shows no data and the graph shows no stock.
I want the graph to show a straight line until the next transaction shows a change to the stock level.