G
Guest
Hello everyone,
I have a problem in a sql query used in MS ACCESS. I intend to get a
updated stocklevel for products. procedure is
Sum(inwardsQuantity)-Sum(outwardsQuantity)=stocklevel. However, my following
table and query design give ms wrong figure on stock levle. Please have a
look:
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
I have a problem in a sql query used in MS ACCESS. I intend to get a
updated stocklevel for products. procedure is
Sum(inwardsQuantity)-Sum(outwardsQuantity)=stocklevel. However, my following
table and query design give ms wrong figure on stock levle. Please have a
look:
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