Calculation in Code Builder for Sum & Subtraction

[recyan]

In case you have not solved above syntax issue, try doing it in the Query Design Grid.

Just a small thing, by Current Balance, I assume Balance which is available for Issuing :

Nz([SumofQtyIn],0)+Nz([SumQtyReturn])-Nz([SumQtyIssued],0) AS CurrentBalance,

CurrentBalance = SumofQtyIn + SumQtyReurn - SumQtyIssued;



SumQtyReurn - Here it should be reflecting the Good Qty Returned ( excluding the Bad Qty Returned ) & not the Total Qty Returned.

Thanks

[braveali]

Hi,

This below qry works fine for me, all i need is to add the information for the DeadItem in qryAvailableBalace, because this Query is showing all the information related to Stock,
I tried but couldn't get it, I will appreciate if you please guide me how should i write some statement to add a field in qryAvailableBalance under this coding: (Thanks)

SELECT
tblItems.ItemID,
tblItems.ItemName,
tblItems.ReOrderLevel,
Nz([SumofQtyIn],0) AS TotalQtyIn,
Nz([SumQtyReturn],0) AS TotalQtyReturned,
Nz([SumQtyIssued],0) AS TotalQtyIssued,
Nz([SumofQtyIn],0)+Nz([SumQtyReturn])-Nz([SumQtyIssued],0) AS CurrentBalance,
IIf([CurrentBalance]<=[ReOrderLevel],"Place Order","Stock level is OK") AS ReOrderStatus
FROM
( ( tblItems LEFT JOIN qrySumofStockIN ON tblItems.ItemID = qrySumofStockIN.ItemID)
LEFT JOIN
qryTotalReturn ON tblItems.ItemID = qryTotalReturn.ItemID)
LEFT JOIN
qrySumofStockOut ON tblItems.ItemID = qrySumofStockOut.ItemID;





QryTotalReturn

ItemID	SumQtyReturn
Antiviurs	1

QryDeadItemReturn

ItemID	QtySumDeadReturn
Antiviurs	1
Desktop Computer	2

QryAvailableBalance

ItemID	ItemName	ReOrderLevel	TotalQtyIn	TotalQtyReturned	TotalQtyIssued	CurrentBalance	ReOrderStatus
1	Antiviurs	0	20	1	4	17	Stock level is OK
2	Desktop Computer	0	10	0	5	5	Stock level is OK
3	CD-R	0	20	0	0	20	Stock level is OK
4	CD-RW	0	0	0	0	0	Place Order
5	DVD-R	0	0	0	0	0	Place Order
6	DVD-RW	0	20	0	0	20	Stock level is OK

[recyan]

Make a copy of your query
qryAvailableBalance .

Give it a name, let us say - qryAvailableBalanceWithDeadStock.

Open this Query in Design View.

Add the query QryDeadItemReturn to this Design Grid.
Join the tblItems ItemID to QryDeadItemReturn ItemID.
Right Click the Join & convert it in to a Left Join by selecting probably, the 2nd option which will be something like "Include all records from tblItems & all records from QryDeadItemReturn where the joined fields are same".

Add the field QtySumDeadReturn from QryDeadItemReturn to the Query fields. If you want a 0 to appear instead of blank fields, use Nz(QtySumDeadReturn, 0).

Now run the query. If it runs fine, take a look at the SQL Design of the Query.

Thanks

[braveali]

Thanks a lot for helping me in this, I have learned a lot from your guidelines.

[recyan]

Glad we could help.

Thanks