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!

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.