Expert: Scottgem - 3/7/2005

Question
Hi Scott

I could not figure out the following problem in my query_currentStockLevel.

Hi, thanks for the response.

Here I can get right figure for Sum(Inwards.Quantity), or Sum(Outwards.Quantity). The problem is about when I subtract Sum(Outwards.Quantity) from Sum(Inwards.Quantity), I got some wrong figure.

To make it clear, lets have a fresh start as explained as follows:

Tbl_Products

ProductID   ProductCode   ProductName
4   56987       Australia Wine
5   50      Chinese high spirit
6   232323      Queensland Beer



Tbl_TransInwards

TransactionID   ProductID          Quantity   
1         4      444
2         5      555
3         6      666
4         4      44
5         5      55
6         6      10

Tbl_TransOutwards

TransactionID      ProductID          Quantity   
1         4      44
2         5      55
3         6      66

*Above three tables be related through ProductID      

Query_TOTALINWARDS

SELECT P.ProductID, P.ProductCode, P.ProductName, Sum(TI.Quantity) AS TOTALINWARDS
FROM Products AS P LEFT JOIN TransInwards AS TI ON P.ProductID=TI.ProductID
GROUP BY P.ProductID, P.ProductCode, P.ProductName;

Run the above query, result:

ProductID   ProductCode ProductName TOTALINWARDS
4   56987   Australia Wine     488
5   50   Chinese high spirit  610   
6   232323   Queensland Beer     676

Query_TOTALOUTWARDS

SELECT P.ProductID, P.ProductCode, P.ProductName, Sum(TO.Quantity) AS TOTALOUTWARDS
FROM Products AS P LEFT JOIN TransOutwards AS TO ON P.ProductID=TO.ProductID
GROUP BY P.ProductID, P.ProductCode, P.ProductName;

Run the above query, result:

ProductID   ProductCode ProductName   TOTALOUTWARDS
4   56987   Australia Wine     44
5   50   Chinese high spirit  55
6   232323   Queensland Beer      66

*Up to now, everything goes all right!

Query_currentStockLevel

SELECT P.ProductID, P.ProductCode, P.ProductName, Sum(TI.Quantity) AS TOTALINWARDS,
Sum(TO.Quantity) AS TOTALOUTWARDS,
(TOTALINWARDS-TOTALOUTWARDS) AS StockLevel
FROM Products AS P, TransInwards AS TI, TransOutwards AS TO
WHERE P.ProductID=TI.ProductID and P.ProductID=TO.ProductID
GROUP BY P.ProductID, P.ProductCode, P.ProductName;

Run above query, result:

ProductID ProductCode  ProduceName OTALINWARDS         TOTALOUTWARDS   StockLevel
4   56987   Australia Wine   488          88          400

5   50   Chinese high spirit   610          110          500

6   232323        Queensland Beer   676          132   544

Problem: You can see from here, the above query generates wrong TOTALOUTWARDS figures for all Product ID 4,5,6.
Those figures double their correct one. I.E. 44 becomes„³88;    55  becomes„³110, 66 becomes„³132.
No doubt, there is a mistake in the above Query_currentStockLevel
    
    Any precious advice?

Thanks very much  

Answer
Well First I don't understand why you have 2 separate transactions tables. You should only have ONE table with a field identifying the transaction as inwards or outwards. I would further use a lookup table for inwards and outwards like so:
tblTranstype
TransTypeID.....Transtype
1...............Inwards
-1..............Outwards

From there, you can use this query as your first step (name it qryStockCalc1):
SELECT ProductID, Qty AS Quantity * TransTypeID
FROM tblTransactions;

This will give you a listing of each Product with a positive value for Inwards and a negative value for outwards. Then all you need is one more step:

SELECT ProductID, Sum(QTY)
FROM qryStockCalc1
GROUP BY ProductID;

That should give you the current stock of each Product. You can add a join to the Products table to get the product names. But this essentially does the job for you.

HTH
Scott<>