Thread: Total stock calculation query

Hello,

I was trying to set-up a query that will ask for a part number from tblPartNumber and sum the stock for this part according to transaction type in tblTransactions.

This is the code I use for suming it.

TotalStock: Sum(IIf([TranType]="Arrival",[Qty],0)+IIf([TranType]="InvIn",[Qty],0)+IIf([TranType]="CountIn",[Qty],0)-IIf([TranType]="InvOut",[Qty],0)-IIf([TranType]="CountOut",[Qty],0)-IIf([TranType]="Despatch",[Qty],0))

tbl Transaction:
PartNumber; Qty; TranType;

Do you know of a top of your heads if there is any basic mistake in a code?
If anyone has any idea please let me know.

Thanks for reading this
Simon

Not quite sure, but is the SUM function necessary ?
You are adding/substracting a number of IIf conditions, the SUM seems redundant

Simon,
Download the Northwind Database, there are plenty of examples there for inventory. It is free from Microsoft. If you have questions after looking at that let me know.

Inventory can be an ugly thing. If you have multiple parts that make up one assembly and possibly have multiple parts that make up a part listed in an assembly you end up having to write recursive code. (Fun to learn though imo)

Hope the Northwind DB helps you,

Dan