Calculation on two fields

[braveali]

Hello,

I have created an stock entry database, and faced the following problems, i will appreciate your help in it.

1. I have created an (Access 2010) query for StockIN (please see the attached file), the files shows how many items i have available in stock table.

2. I have Created an other query for StockOut (please see the attached file), this files shows how many items have been out to company officials.

3. I have Created a 3rd Query for Stock Balance (please see the attached file)

Problem:



i have input 3 items (as you can see in stock in), and dispatched 2 items, but my Query is showing the available balance of two 2 items, the 3rd item is not showing in the balance query.

i have attached the structure of my balance query as well..

what i believe is that my balance query is only showing me the items i have subtracted from incoming stock, the 3rd item is not appearing in the balance query because it did not issue to someone.

but it must come in the balance query so that one can know that we have 1 camera in stock.

i hope i could convey my question in an understandable way

thanks

[recyan]

Check on the join type.

Thanks

[braveali]

join types are fine ! any other possible way please?

[recyan]

Try below & see if it gives some guidelines :

SELECT 
    STIN.ItemID, 
    STIN.ItemName, 
    STIN.SumOfQty, 
    Nz([OutQty],0) AS OutQuantity, 
    [SumOfQty]-Nz([OutQty],0) AS Balance
FROM 
    STIN 
    LEFT JOIN 
    STOUT 
    ON 
    STIN.ItemID = STOUT.ItemID;

Thanks

[braveali]

Thanks for your reply, such as i am a new user, please guide where should i type this select statement ?

[recyan]

Copy paste the query in the Query Design SQL pane or you can design it in the Query Design Grid & then save it under some name & run it.
It is just another another query, the same as other queries that you have designed.

Thanks

[braveali]

Thats worked 100%

I used the given Select Statement:
SELECT
STIN.ItemID,
STIN.ItemName,
STIN.SumOfQty,
Nz([OutQty],0) AS OutQuantity,
[SumOfQty]-Nz([OutQty],0) AS Balance
FROM
STIN
LEFT JOIN
STOUT
ON
STIN.ItemID = STOUT.ItemID;

I will appreciate if you please guide this select statement in brief, i can understand that we had Selected ItemID,ItemName,SumofQty what is Nz([OutQty],0) as outQty ?
is it a variable? Nz? which store 0 value and then this value store in outqty? and then we subtracted sumofQty - Nz and it stor in balance?

From
Stin // i know this is a Query stored my StockIn

what is Left Join?

Stout // i know this is a Qury stored my StockOut

ON?

Stin.ItemID = StOut.ItemID; ?

please guide for my knowledge and future working.

thanks for your help...

[braveali]

An other Quick Questions

can i display this balance on a form button as well?

for instence; i have created a from for stock out, and when every we select any item from dropdown menu, its balance should be show on a field?






Right now i have created a module (with the help of my friend)


Public Function BalanceCheck(I As Integer) As Long
Dim t As Long
t = Nz(DSum("Balance", "ItemBalance", "ItemID =" & I), 0)
BalanceCheck = t



and then called it on this botton!

End FunctionPrivate Sub ItemID_AfterUpdate()

AvailableQty.Value = BalanceCheck(ItemID.Value)

End Sub

[recyan]

I will appreciate if you please guide this select statement in brief,

For understanding Joins :

http://www.w3schools.com/sql/sql_join.asp

read the above & the next few referring to Inner Join, Left Join, Right Join, Full Join.

You had a INNER JOIN in your initial query.
An INNER JOIN will only display records which exist in both the tables on the JOIN field.

________________________________

For Nz()

http://office.microsoft.com/en-us/ac...001228890.aspx
________________________________

OutQuantity : Nz([OutQty],0)

The above is not needed in your Query.

You can as well have

OutQty

instead.

We have made it that way, so that the Query Results display 0, wherever there are null values, instead of blanks.
________________________________

Balance: [SumOfQty]-Nz([OutQty],0)

The above is to use a 0 wherever there is a null value, as you cannot subtract a Null value from a Number.

________________________________

What I would suggest is :
In your original query, which you had first posted,
1) Change the Join type from INNER to LEFT JOIN & see what happens.
2) Next, Change the OutQty to OutQuantity : Nz([OutQty],0), see what happens
3) Finally change Balance: [SumOfQty]- [OutQty] to Balance: [SumOfQty]-Nz([OutQty],0) & see what happens.


Note : When we are using the LEFT JOIN, we are assuming that all the ITEMIDs present in "STOUT" are definitely present in the "STIN" & perhaps some more in "STIN".

can i display this balance on a form button as well?

for instence; i have created a from for stock out, and when every we select any item from dropdown menu, its balance should be show on a field?

Yes it can definitely be done.
I am not in to Forms & Modules.
In a simple way, it can be done by basing your form Balance field on a query, which takes the Form field ItemID as criteria & returns the Available Quantity.

Note : This post appears as SOLVED, hence there will be very few people looking at it. Would suggest starting a new thread for this present question of yours.

Thanks