Calculating fields

[yanela]

I have different tables that are linked to each other (Tabl
Relationship). It has Quantity In and Quantity Out field on a table
How can it be done so that when a value is entered to the Quantity Out
it is lessed or substracted to the Quantity In when an employee retur
the items done. Like for this day, I got 5 items that are to b
processed and the next day or someday, I will bring back 4 that ar
finished product. So there would be 1 more left for me to finish. Th
system should also not allow enter a value in the Quantity In that i
greater than Quantity Out. Please let me know if I you have questions
I'll try to make it clear as possible. Thanks much..

 

[Allen Browne]

Store the transactions, not the current quantity.

The table will have fields like this:
ProductID which product this is
Direction -1 for Out, 1 for In.
Quantity the number out or in.

To get the quantity of each product, you can then create a query, depress
the Total icon on the toolbar (Sigma icon). Group by the ProductID, and Sum
this expression (which you type into the Field row in query design):
Direction * Quantity