Left Join doesn't display the records I expected.

[Aldred]

Hi all,
I have a SQL query with all left joins. However, the left most table tClient wasn't displayed if it gets null from the right table.

The linking is something like tClient --> tPOin --> tPOItems --> tDelivered

The query is exactly like this:

SELECT EName, Sum(tPOItems.Charge*tDelivered.DQuantity) AS Amount 
FROM tClient left JOIN (tPOin left JOIN (tPOItems left JOIN tDelivered ON tPOItems.ID=tDelivered.POItemsID) ON tPOin.ID=tPOItems.POinID) ON tPOin.ClientID=tClient.ID WHERE InvoiceID Is Not Null And CalcMonth=Forms!fGenMonthlyStatement.bCalcMonth And CalcYear=Forms!fGenMonthlyStatement.bCalcYear 
GROUP BY tClient.EName; 
 

The result is, for example:

Company A, $68012
Company C, $133000

However, I'd like to have Company B displayed but it doesn't.

Can someone please help me.

Thanks in advance.

[TheSmileyCoder]

My guess would be that it is related to the:

WHERE InvoiceID Is Not Null 

Have company B placed any orders?

[Oralloy]

Try removing the grouping function SUM, and just display all the interesting columns.

Then, when you've massaged the querty to give you the desired records, then you can re-implement the grouping SUM.

Cheers!