How to create a sum which adds new values to an accumulated sum

[alcaEli]

Hi,

I am working with an access database. There are 3 columns, The Inventory, The amount to add or subtract from Inventory and The Updated inventory. The updated inventory should display the sum of the other two columns plus its previous accumulated sum but is not doing that because the privous result is not being held or included in the calculation.

For example the inventory is 10, amount to be added is 2, the updated inventory shows 12 which is ok;


however if on a second test the amount to be added is 5 and the inventory is still 10, then the updated inventory shows 15 abd it should be 17 because the last value in the updated inventory was 12 plus 5 is 17. Please help me

AlcaEli

[NeoPa]

I think you need to look again at your design.

The reason you can't get it to work is that your design doesn't makes sense. Your totals shouldn't be stored in individual records.

You may benefit from looking at Database Normalisation and Table structures.

[AlcaEli]

Thanks!!

I believe my design is fine. I think I just need the right function or commands to keep an addition that includes the previous sum.

[MMcCarthy]

@AlcaEli

Your problem is in your design as NeoPa says. You don't store calculated values in a database. It leads to the very data integrity problems you are having.

There is no formula to do as you describe. It doesn't make any sense.

If I have an inventory of 10 and then I add two why would I keep the value 10? I would just update the value to 12. Then when I add 5 more I would update it to 17. That way I know my inventory stock. I could then query my stock sold against my inventory to find available stock.

[NeoPa]

AlcaEli:
I believe my design is fine. I think I just need the right function or commands to keep an addition that includes the previous sum.

It's going to be very hard to help you then.

I dare say I have a little more experience than you have in this area, and I'm sure your design will not enable you to do what you want straightforwardly. I'm sure if you apply a little bit of basic logical thinking you'll see why I say what I do.

Good luck anyway.