Running Total Problem

[gower180]

Hi

I a having a problem trying to keep a running total on paper stock, i think it must be pretty simple but i just can't work it out. I have got 3 fields one is 'amount in stock' the other is 'remove stock' the other 'add stock'.

I want to have amount of stock starting on 0 and then be able to enter an amount in add stock say 1000 and then be able to enter say 100 in remove stock and then amount in stock should show 900. The problem i am having is that when i clear 'remove stock' and 'add stock' 'amount in stock' goes to zero wheras i want it to remember that it was on 900.

Hope this makes sense and that someone can help.

Thanks

paul.

[Christine Sephton]

Please be sure that the fields "remove stock" and "add stock" are defined as "number" fields. You can either define "amount in stock" as a number field, with an auto calculation, or as a calculation field. (I personally recommend a calculation field if you may update stock totals in other layouts, or will eventually get totals from other tables.) When you go into the calculation definition (regardless if it is a number field with auto calculation, or a calcuation field) be sure to uncheck the box in the calculation area that says "Do not evaluate if all referenced fields are empty". If you define the field as a calculation, I also recommending that you go into storage options and check "do not store calculation results". That should resolve your issue.

[gower180]

Hi thanks for the reply,

I have now got remove stock and add stock as number and have got amount in stock as a calculation with do not evaluate if all referenced fields are empty unticked, and in storage options have got do not store calculation results ticked, the caluculation i have for amount in stock is 'amount in stock = + add stock - remove stock.

When i clear add and remove stock amount in stock still goes blank.

Paul.

[LaRetta]

"I have got 3 fields one is 'amount in stock' the other is 'remove stock' the other 'add stock'."

Hi Paul,

You have not described the tables involved but an inventory should have the following fields:

1) Quantity (number) which holds a plus or minus amount indicating stock in or stock out.

2) Total of Quantity and set the summary to running totals.

Ahem ... you DO have individual records, right? I truly hope you are not using a single record in the Product table for your inventory tracking.

[comment]

the caluculation i have for amount in stock is 'amount in stock = + add stock - remove stock.

The calculation field's formula should be =

add stock - remove stock

In addition to the calculation field, you also need a summary field, defined as Total of [amount in stock], running.

[gower180]

Hiya,

I have 1 table and have only populated it with 1 record at the moment whilst i try to get it to work, i have tried everything below but to no avail everytime i clear remove stock or add stock the amount of stock also dissapears and forgets what it was previous. If its easier for me to email it to someone to have a look please let me know.

Thanks again

Paul.

[comment]

That's how it works. Data is stored in records. If you clear the data, it's gone.

[gower180]

Ok maybe i'm trying to do something that cannot be done then. Thanks for the help anyway.

[Christine Sephton]

I think I understand what you are trying to do now.

Try this:

If (IsEmpty(add stock) and IsEmpty(remove stock), 0, add stock - remove stock)

That should get you to where you want to be.

Thanks,

Christine

[Christine Sephton]

Paul it sounds more like you want to be able to update a stock total just from adding or subtracting stock via a field. It can be done, but it appears you are going the long and hard way around it. You would need to write a script trigger, and make all three fields a number field.

Have you considered just having another table that will include both stock addition and subtraction. That away you can just simply just add the related records of stock changes to get your total?

Christine

[gower180]

Thanks Christine,

I tried your first option and that done the same thing, i think this has got me stumped ! i would have thought that i could just have a running total .

Paul.

[LaRetta]

You can HAVE a running total, Paul, when you have records. As I suggested, if each record is a plus or minus, just as in a checkbook, then the running summary takes care of itself.

If you wish to use a radio button to designate whether the amount is plus or minus, you can (and have the auto-enter toggle the amount using Sign) , but why bother?

[gower180]

What i was after doing was having a record for each coloured paper ie one record for white another for blue etc with stock totals on each form and a field for taking stock out when its used and adding stock to it once it is ordered in. I guess i am just going to have to have a basic number field that i have a total in and just take off and put stock on it manually.

Paul.

[comment]

What i was after doing was having a record for each coloured paper ie one record for white another for blue etc with stock totals on each form and a field for taking stock out when its used and adding stock to it once it is ordered in.

That's almost correct: you should have a record for each product in a Products table. However, you also need a record (not a field) for each transaction that affects the inventory. These records should be in another table, and their total will give you the current stock level.

[gower180]

Ok so to add or remove stock would i have to update the 'other table' so basically the products table is just showing the data from the other table but not editable ?

[comment]

Maybe - I am not sure I understand what exactly you mean by "update". To add stock, you must create a record of adding a stock, for example:

Date: Sep 16, 2011

ProductID: 123

Quantity: 1000

Then you may have another record removing stock, e.g.

Date: Sep 17, 2011

ProductID: 123

Quantity: -100

And if you summarize these two transactions (both affecting the same product), you'll get 900.

[gower180]

Get it now, as Christine said above think i was complicating it too much. Thanks everyone for your help much appreciated.