AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
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
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;
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.
