Inventory table: field that calculates ongoing balance? 1

[CharlesH]

If I have a table that tracks inventory, and each record denotes a transaction that either adds or subracts from inventory. How do I create another field that automatically calculates the balance for each record?

The calculation would entail adding or subtracting from the current record, as well as adding from the previous record.

For example:

Date Add/Subtract Balance*
1/1 +100 100
2/1 +200 300
3/1 -50 250
4/1 +100 350

*How do I create a query that automatically creates the balance column.

Thanks for your help.

 

[Geekmomz]

In a query:

SELECT Inventory.ProdID, Sum(Inventory.Qty) AS SumOfQty
FROM Inventory
GROUP BY Inventory.ProdID;

or, in a calculated field on a form or report:

=Sum([Qty])

 

[JerryDennison]

The ease with which you can do this is dependent on where you need to display it. In a report it's very simple, use the running sum property of an unbound control. Add a control in the detail section of your report with the following as it's controlsource property:

=[QtyFieldName]

Then set it's Running Sum property to the appropriate value. For example, if you have a group level that you want to show a running sum for then re-zero when the group level value changes use Over Group, otherwise use Over All.

 

[CharlesH]

Thanks for the response..but how do I create a running balance calculation, which shows the standing balance for each date (or record).

I believe your solution only calculates the balance for the entire table, or last date. The query should come out:

Date Balance
1/1 100
2/1 300
3/1 250
4/1 350

 

[CharlesH]

Unfortunately, I need the balance information in either a table or query, not in a report.

 

[RichUK]

You need a sequential key field to do this effectively, do you have one?

 

[JerryDennison]

Why do you need this in only a query or table? For display on the monitor? Anyway, this invloves the use of a subquery. Follow the link below to see an example, as RichUK points out you must have a unique sequential field for this to work (by sequential I mean that one record field's value is greater, but not necessarily the very next number, than the record preceding it).

http://ourworld.compuserve.com/homepages/attac-cg/AqryTip.htm

 

[CharlesH]

Thanks Geekmomz, JerryDennison, and RichUK. This formula worked great:

RunningSum: (Select Sum (OrderTotal) FROM [Orders] as Temp
WHERE [Temp].[OrderID] <= [Orders].[OrderID])

Brilliant.

 

[JerryDennison]

You're welcome.

 

[Geekmomz]

Hope the rest of it goes smoothly.