MS-Access: Calculate Stock From Multiple Table.

 Suppose we have 3 table in an MS-Access database.

Sample File Download Link.

1. Table 1 tblItems have items details information. Here ItemCode is Primary Key field.

2. Table 2 tblPurchase contain transactions of material purchasing.

3. Table 3 tblSales contain sales transactions.

Output Result:

Now we want to calculate stock position from these 3 tables. We will take items details from tblItems, purchase quantity from tblPurchase, sales quantity from tblSales for each item then minus sales quantity from purchase quantity to show stock quantity.

Below is query to calculate stock from these 3 table.

SELECT TblItems.ItemCode, TblItems.Description, TblItems.ItemClass, TblItems.ItemCategory, Nz(TblPurchaseAgg.pQuantity,0) AS PurchaseQuantity, Nz(TblSalesAggs.sQuantity,0) AS SalesQuantity, (Nz(TblPurchaseAgg.pQuantity,0)-Nz(TblSalesAggs.sQuantity,0)) AS Stock, TblItems.Unit FROM (TblItems INNER JOIN (SELECT TblPurchase.ItemCode AS ItemCode, Sum(TblPurchase.Quantity) AS pQuantity FROM TblPurchase GROUP BY TblPurchase.ItemCode)  AS TblPurchaseAgg ON TblItems.ItemCode = TblPurchaseAgg.ItemCode) LEFT JOIN (SELECT tblSales.ItemCode AS ItemCode, Sum(tblSales.Quantity) AS sQuantity FROM tblSales GROUP BY tblSales.ItemCode)  AS TblSalesAggs ON TblItems.ItemCode = TblSalesAggs.ItemCode;

Above query will show items which have purchase transactions as we use INNER JOIN to tblPurchase. If if we want to see all the items from tblItems which has purchase transactions or not then we have to use LEFT JOIN to tblPurchase. Basically left join will select all the items from tblItems and the items are equal to tblPurchase. So, query will be like below-

SELECT TblItems.ItemCode, TblItems.Description, TblItems.ItemClass, TblItems.ItemCategory, Nz(TblPurchaseAgg.pQuantity,0) AS PurchaseQuantity, Nz(TblSalesAggs.sQuantity,0) AS SalesQuantity, (Nz(TblPurchaseAgg.pQuantity,0)-Nz(TblSalesAggs.sQuantity,0)) AS Stock, TblItems.Unit FROM (TblItems LEFT JOIN (SELECT TblPurchase.ItemCode AS ItemCode, Sum(TblPurchase.Quantity) AS pQuantity FROM TblPurchase GROUP BY TblPurchase.ItemCode)  AS TblPurchaseAgg ON TblItems.ItemCode = TblPurchaseAgg.ItemCode) LEFT JOIN (SELECT tblSales.ItemCode AS ItemCode, Sum(tblSales.Quantity) AS sQuantity FROM tblSales GROUP BY tblSales.ItemCode)  AS TblSalesAggs ON TblItems.ItemCode = TblSalesAggs.ItemCode;

And if anyone want to see only those items which stocks are available. In an another word which stock is zero, we don't want see those in query result then use following query.

SELECT TblItems.ItemCode, TblItems.Description, TblItems.ItemClass, TblItems.ItemCategory, Nz(TblPurchaseAgg.pQuantity,0) AS PurchaseQuantity, Nz(TblSalesAggs.sQuantity,0) AS SalesQuantity, (Nz(TblPurchaseAgg.pQuantity,0)-Nz(TblSalesAggs.sQuantity,0)) AS Stock, TblItems.Unit FROM (TblItems LEFT JOIN (SELECT TblPurchase.ItemCode AS ItemCode, Sum(TblPurchase.Quantity) AS pQuantity FROM TblPurchase GROUP BY TblPurchase.ItemCode)  AS TblPurchaseAgg ON TblItems.ItemCode = TblPurchaseAgg.ItemCode) LEFT JOIN (SELECT tblSales.ItemCode AS ItemCode, Sum(tblSales.Quantity) AS sQuantity FROM tblSales GROUP BY tblSales.ItemCode)  AS TblSalesAggs ON TblItems.ItemCode = TblSalesAggs.ItemCode WHERE ((((Nz(TblPurchaseAgg.pQuantity,0)-Nz(TblSalesAggs.sQuantity,0)))>0));

Queries Download As Text File.

If you have any query then please let us know by comments. Also you can send mail to harun24hr@gmail.com

My question to stackoverflow.

Read More