I have been trying for several days to work through the syntax of the DSUM function in an access query to create a running total of an inventory project of part totals that is tied to both the Part Number (PartNum - Short text) and the Lot Number (LotNumber - Short text) of that part. The table is InventoryLog, and the field i'm looking to sum is Total which is a calculated field of the Quantity In (QtyIn - Number) minus the Quantity Out (QtyOut -Number) in each record.
The query syntax I am using is:
RunningTotal: DSum("Total","I nventoryLog","P artNum='" & [Forms]![Inventory Log]![PartNum] & "' AND [LotNumber]= '" & [Forms]![Inventory Log]![Lot Number] & "'")
The SQL statement is:
SELECT DSum("Total","I nventoryLog","P artNum='" & [Forms]![Inventory Log]![PartNum] & "' AND [LotNumber]= '" & [Forms]![Inventory Log]![Lot Number] & "'") AS RunningTotal, InventoryLog.Pa rtNum, InventoryLog.Lo tNumber, InventoryLog.Qt yIn, InventoryLog.Tr ansDate, InventoryLog.In itials, InventoryLog.Di sposition, InventoryLog.Wo rkOrder, InventoryLog.Qt yOut, InventoryLog.Co mments, InventoryLog.To tal
FROM InventoryLog
WHERE (((InventoryLog .PartNum)=[Forms]![Inventory Log]![PartNum]) AND ((InventoryLog. LotNumber)=[Forms]![Inventory Log]![LotNumber]));
When the query is run I get the final total of the PartNum/LotNumber combination, but not a running total for each record.
I have an intermediate knowledge at best of access, but i'm certainly no expert. Any help you can offer will be very appreciated.