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...
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!