how to calculate current stock (1 Viewer)

[james7705]

Hi
I have a query (Inventory Transactions Extended) in which i am trying to caculate current stock.
I have a form (Inventory Transactions Form) where i either add or remove Inventory Items.
I have used this statement to create a new field (Actual Quantity) to calculate current stock based on stock been added and removed:

Actual Quantity: IIf([Transaction Types].[Add/Remove]="Addition";[Inventory Transactions]![Quantity];-([Inventory Transactions]![Quantity]))

for some reason it does not work. It is not calculating current stock. If i remove stock it shows a negative amount based on the amount i removed, and when i add stock it shows a positive amount based on what i added

I have attached a sample.

View attachment Sample.accdb

 

[Mihail]

The real answer for you is here:
Database Normalization and Table Structures - Microsoft Access / VBA
or in similar articles.

 

[jdraw]

see this article by Allen Browne