0

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 by blocker

2
Contributors
3
Replies
13
Views
2 Years
Discussion Span
Last Post by blocker
0

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;
0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.