running sum in a form

[dennisr]

Access 2003 - each record has how much is added or removed from inventory. On the form (or query) I want to enter a starting inventory amount and have the running inventory show at each record row. Easy to do in excel but how can I do it in Access? Inventory = Previous Inventory + added - removed. Records will be sorted by date and time (may not be in Record ID order).

 

[Taruz]

Hi..

This way, you can get the previous record total by using..

select 
              date_field, 
              record_ID, 
              field1, 
              field2,
               (
                select sum(field1-field2) from table_name as b 
                         where  date_field&record_ID<=a.date_field&a.record_ID  ) as RunSum
from table_name AS a
order by  1,2

 

[dennisr]

Thanks Taruz, that worked. I never would have figured that ( a running sum for a query or form) out on my own.