Selecting data from multiple tables

[jonnyuk3]

I Have three tables - Product, GoodsIn, GoodsOut

Product - ProductID, ProductCode, Description
GoodsIn - GoodsInID, Date, ProductID, Qty
GoodsOut - GoodsOutID, Date, ProductID, Qty

I am trying to create a query that combines the data from these tables to give me a total of goods in and goods out for each product.

This works ok if there is a record for each ProductID in each of the tables.

However if ProductID 1, for example, exists in both the Product and GoodsIn table but does not have a corresponding record in the GoodsOut table then no data is returned for that record.

How can I just return a value of 0 for the quantity if no record for the ProductID exists in either the GoodsIn or GoodsOut tables.

Any help would be appreciated.

jonnyuk3

 

[jzwp22]

I would first create 2 total queries (one for goodsIn and the other for goodsout)

query name: qrySumIN
SELECT GoodsIn.ProductID, Sum(GoodsIn.qty) AS SumOfqtyIN
FROM GoodsIn
GROUP BY GoodsIn.ProductID;

query name: qrySumOUT
SELECT GoodsOut.ProductID, Sum(GoodsOut.qty) AS SumOfqtyOut
FROM GoodsOut
GROUP BY GoodsOut.ProductID;

Now create a third query using the products table and the two sum queries above. The key to doing this is making sure you use the correct join type between the table and each query. You want all of the records from the product table and only those from qrySumIn that match; this is a left join. Then you would do the same for the qrySumOut. This query will give you what you want except that if there is no matching out data for a product you will have a null result not a zero. To handle displaying the zero, you have to use some functions to test if the query results in a null value and if so, put a zero if not put the actual value. To do this you would nest the IsNull() function within the IIF() function. The final query would look like this:

SELECT Product.ProductID, Product.txtProdName, IIf(IsNull([SumOfqtyIN]),0,[SumOfqtyIN]) AS qtyIn, IIf(IsNull([SumOfqtyOut]),0,[SumOfqtyOut]) AS qtyOut
FROM (Product LEFT JOIN qrySumIN ON Product.ProductID = qrySumIN.ProductID) LEFT JOIN qrySumOUT ON Product.ProductID = qrySumOUT.ProductID;


As a general tip, you use a field called "date" in both your goodsin and goodsout tables. "Date" is a reserved word in Access, so I would suggest you change your date field names; goodsInDate and goodsOutDate would probably be appropriate.

 

[jonnyuk3]

jzwp22, thanks for your help.

It was the left join, i'd never used that before.

Obviously still got a lot to learn!!!

jonnyuk3

 

[jzwp22]

You're welcome. I'm glad I was able to help out.

 

[Atomic Shrimp]

I think this presents a fairly strong case for your goodsin and goodsout tables to be merged into a single transactions table, with an extra column to describe the type of transaction (i.e. goods in, out, or others such as stock adjustment, etc)