Query to calculate Quantity on Hand

[Yam84]

Hello:
I am developing a check In/Out system. when a check out occurs, stock is decremented by the amount that is checked out, and then incremented when that product is checked back in. I have a query that determines that amount of a product that is in stock.
SELECT [Check InOut].checkOutInID, [Check InOut].checkOutQuantity, [AcqDetail Query].StockTake.productID, [AcqDetail Query].SumOfstockQuantity, (([AcqDetail Query].[SumOfacqQuantity])-[Check InOut].[checkOutQuantity]) AS Expr1
FROM (Products INNER JOIN [AcqDetail Query] ON Products.[productID] = [AcqDetail Query].[AcqDetail].[productID]) INNER JOIN [Check InOut] ON Products.[productID] = [Check InOut].[productID];
This query however, does not calculate past this point. It gives me the incorrect output for Expr1 (quantity on hand), if a product is checked out more than once, as opposed to decrementing from the new stock amount, it deducts from the original one. For instance:

checkOutInID/ Item/ Quantity Out / Product/ SumOfstock/ Expr1
73/ Hammer/ 1/ Hammer/ 7/ 6
74/ "1 1/2"" Putty Knife"/ 1/ "1 1/2"" Putty Knife"/ 2/ 1
75/ Hammer/ 2/ Hammer/ 7/ 5
In record 75, Expr1 should read 4 and SumOfstock should read 6, because in record 73, after the initial hammer was checked out, there were 6 left. My plan is that when an item is checked back in, the stock will increase by the number of items added back to stock. I have been unable to achieve this. Additionally, how will I make it so that when something is checked out stock is decremented, but when it is checked baack in, stock is incremented? Any insight will be appreciated.

 

[ByteMyzer]

Sounds like you may need to normalize/simplify your tables and queries. Consider the following scenario:

Two tables:

tblProduct
----------
ProductID
ProductDescription

tblTransaction
--------------
TransID
ProductID
TransType (I = In; O = Out)
Quantity

...populated with the following data:

tblProduct
----------
ProductID | ProductDescription
--------------------------------------------
1         | Hammer
2         | Wrench
3         | Pliers

tblTransaction
--------------
TransID | ProductID | TransType | Quantity
------------------------------------------
1       |         1 | I         | 1
2       |         2 | I         | 1
3       |         3 | I         | 1
4       |         2 | O         | 1
5       |         3 | O         | 1
6       |         1 | O         | 1
7       |         2 | I         | 1
8       |         1 | I         | 1

...and a query, qryStock:

SELECT T1.ProductDescription,
       Sum(Iif(T2.TransType='I',T2.Quantity,-T2.Quantity) AS StockQty
FROM tblProduct AS T1
INNER JOIN tblTransaction AS T2 ON T1.ProductID = T2.ProductID
GROUP BY T1.ProductDescription
HAVING Sum(Iif(T2.TransType='I',T2.Quantity,-T2.Quantity)>0;

The query output would give you the following as stock on-hand:

qryStock
--------
ProductDescription | StockQty
-----------------------------
Hammer             | 1
Wrench             | 1

 

[Yam84]

Normalized Correctly

Thank you for your response.

I have remedied the issue by creating two queries. One calculates the amount of a product currently checked out. The other uses that amount and subtracks it from the sum of the current stock for each product which calculates the amount on hand.

I need assistance however, with one other issue. I would like for when the product is checked back in, for it to increase the stock amount. Any ideas how to accomplish this? My idea was to place an IFF statement in the check out date column which will check to see if the user currently has any items checked out, if they do, a message will pop up saying that Until they return or pay for the currently checked out item, they can not check out another item. Is this approach viable?

 

[mjlee]

...and a query, qryStock:

SELECT T1.ProductDescription,
       Sum(Iif(T2.TransType='I',T2.Quantity,-T2.Quantity) AS StockQty
FROM tblProduct AS T1
INNER JOIN tblTransaction AS T2 ON T1.ProductID = T2.ProductID
GROUP BY T1.ProductDescription
HAVING Sum(Iif(T2.TransType='I',T2.Quantity,-T2.Quantity)>0;

Sorry if it seems a stupid question, but how do you set up this query using the above code? I've hardly ever done a query any more complicated than a search and any help would be much appreciated.

Thanks in advance,

Michael.

 

[MdHaziq]

Same here,
How does the query code work?

 

[Stanski21]

Try this...

http://www.dummies.com/programming/sql/how-to-use-sql-with-microsoft-access/

 

[MdHaziq]

I have done what ByteMyzer did but I face a problem when trying to save the qry.

The error message says,

Could someone help me see the code whether it is missing any operator or spelling?

Actually, I do not understand how to read the code.

 

[Stanski21]

I think you've missed a closing bracket after T2.Quantity, -T2.Quantity)
You need to close out the IIF and close out the SUM too.

 

[Stanski21]

Try this one...

SELECT T1.ProductDescription,
       Sum(Iif(T2.TransType='I',T2.Quantity,-T2.Quantity)) AS StockQty
FROM tblProduct AS T1
INNER JOIN tblTransaction AS T2 ON T1.ProductID = T2.ProductID
GROUP BY T1.ProductDescription
HAVING Sum(Iif(T2.TransType='I',T2.Quantity,-T2.Quantity))>0;

 

[Minty]

You are missing a closing ) at the end I think. If you could cut and paste the query here or view it in the query designer it would tell you.

Edit - oops took too long typing that ...

 

[rayhan1500]

Try this one...

SELECT T1.ProductDescription,
       Sum(Iif(T2.TransType='I',T2.Quantity,-T2.Quantity)) AS StockQty
FROM tblProduct AS T1
INNER JOIN tblTransaction AS T2 ON T1.ProductID = T2.ProductID
GROUP BY T1.ProductDescription
HAVING Sum(Iif(T2.TransType='I',T2.Quantity,-T2.Quantity))>0;

if issue quantity is greater than received quantity , negative figure not showing, pls help me,

 

[rayhan1500]

Try this one...

SELECT T1.ProductDescription,
       Sum(Iif(T2.TransType='I',T2.Quantity,-T2.Quantity)) AS StockQty
FROM tblProduct AS T1
INNER JOIN tblTransaction AS T2 ON T1.ProductID = T2.ProductID
GROUP BY T1.ProductDescription
HAVING Sum(Iif(T2.TransType='I',T2.Quantity,-T2.Quantity))>0;

if issue quantity is greater than receiving query not showing negative figure, please help me

 

[rayhan1500]

if issue quantity is greater than received quantity , negative figure not showing, pls help me,

stock quantity may be nagative figure sometimes.