Running total within a category in Access

I am trying to calculate a running total in Microsoft Access and could really need some help.

Basically I have the below table containing three columns:

1. Item
2. Inventory Position
3. Multiple

I need a four-column (let’s call this OnhandAgg) that does a running total calculation per item.

The first record within an item should start the running total calculation with Inventory position + Multiple. Thereafter the remaining records within the items should be a running total calculation with last record value + multiple.

When having looped through all records within an item it should restart the running total.

The end result should look something similar to this – I have added an auto number also which should also be a part of the first table.

Answer

I can’t tell about its efficiency but it works:

select t.*,
(select sum(Multiple) from tablename where Item = t.Item and [No] <= t.[No]) +
(select InventoryPos from tablename where Item = t.Item and [No] = (
  select min([No]) from tablename where Item = t.Item)
)
from tablename as t