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","InventoryLog","PartNum='" & [Forms]![Inventory Log]![PartNum] & "' AND [LotNumber]= '" & [Forms]![Inventory Log]![Lot Number] & "'")
The SQL statement is:
SELECT DSum("Total","InventoryLog","PartNum='" & [Forms]![Inventory Log]![PartNum] & "' AND [LotNumber]= '" & [Forms]![Inventory Log]![Lot Number] & "'") AS RunningTotal, InventoryLog.PartNum, InventoryLog.LotNumber, InventoryLog.QtyIn, InventoryLog.TransDate, InventoryLog.Initials, InventoryLog.Disposition, InventoryLog.WorkOrder, InventoryLog.QtyOut, InventoryLog.Comments, InventoryLog.Total
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.