I have the following query that list all chemicals(chemical tbl) and its consumption(receipt tbl). However if the chemical codes are not in receipt tbl, it does not show the chemical name. It should display the name and put the consumption as zero. My codes below:

SELECT Chemical.ChemicalCode, Chemical.ChemicalName, GroupDetails.GroupName, Chemical.LastRate, Chemical.MaximumOrder, Sum(Receipt.ReceivedQuantity) AS ReceivedQty, [Chemical].[MaximumOrder]-Sum([Receipt].[ReceivedQuantity]) AS UsableCIF
FROM (Chemical INNER JOIN Receipt ON Chemical.ChemicalCode = Receipt.ChemicalCode) INNER JOIN GroupDetails ON Chemical.GroupCode = GroupDetails.GroupCode
WHERE (((Chemical.MaximumOrder)>0) AND ((Receipt.ReceivedOn)>=#12/12/2014# And (Receipt.ReceivedOn)<=#12/31/2015#) AND ((Receipt.DestinationPort) Like "*CIF*") AND ((Chemical.IsActive)=True))
GROUP BY Chemical.ChemicalCode, Chemical.ChemicalName, GroupDetails.GroupName, Chemical.LastRate, Chemical.MaximumOrder;

And here is the result...
[img]http://i.imgur.com/MhmsaDw.png[/img]

It should display those chemical names where ReceivedQty is zero too and UsableCIF must be equal to MaximumOrder as there is no ReceiptQty.
thank you for helping!

Edited 1 Year Ago by blocker

LEFT JOIN .. use LEFT JOIN before Groupdetails table to get your desired output

thank you Bhuvan 1!

Ive tried LEFT JOIN but the same result!

    SELECT Chemical.ChemicalCode, Chemical.ChemicalName, GroupDetails.GroupName, Chemical.LastRate, Chemical.MaximumOrder, Sum(Receipt.ReceivedQuantity) AS ReceivedQty, [Chemical].[MaximumOrder]-Sum([Receipt].[ReceivedQuantity]) AS UsableCIF
    FROM (Chemical LEFT JOIN Receipt ON Chemical.ChemicalCode = Receipt.ChemicalCode) LEFT JOIN GroupDetails ON Chemical.GroupCode = GroupDetails.GroupCode
    WHERE (((Chemical.MaximumOrder)>0) AND ((Receipt.ReceivedOn)>=#12/12/2014# And (Receipt.ReceivedOn)<=#12/31/2015#) AND ((Receipt.DestinationPort) Like '%CIF%') AND ((Chemical.IsActive)=True))
    GROUP BY Chemical.ChemicalCode, Chemical.ChemicalName, GroupDetails.GroupName, Chemical.LastRate, Chemical.MaximumOrder;

problem solved. I have removed (Receipt.DestinationPort) Like '%CIF%') in the where clause..

This question has already been answered. Start a new discussion instead.