Records are missing in a query and report

[Papa Echo]

Hello,
I have two tables Tab1 and Tab2 which stand in a 1:n relationship connected over an ID. In Tab1 there is a field A. In Tab2 I have a field B. For a report I need the fields ID and A plus the sum of all B.
My query in the report looks like this:
SELECT Tab1.ID, Tab1.A, Sum(Tab2.B) AS C FROM Tab1 LEFT JOIN Tab2 ON Tab1.ID=Tab2.ID GROUP BY ID, A



In the report I have a textfield: =[A] + Nz([C])

My problem is that some records from Tab1 are missing if Tab2 doesn’t have data with the ID. But the LEFT JOIN means: Include ALL records from ‘Tab1’ and only those records from ‘Tab2’ where the joined fields are equal.

What do I have to change to get all records from Tab1 even if there are no corresponding records in Tab2?

[CJ_London]

you need to do them separately

SELECT Tab1.ID, Tab1.A, C FROM Tab1 LEFT JOIN (SELECT ID, Sum(B) AS C FROM Tab2 GROUP BY ID) T3 ON Tab1.ID=T3.ID

[isladogs]

You haven't assigned an value in Nz if C is null
May be worth doing the same for A as well though I assume that must always have a value

Try

SELECT Tab1.ID, Tab1.A, Nz(Sum(Tab2.B),0) AS C FROM Tab1 LEFT JOIN Tab2 ON Tab1.ID=Tab2.ID GROUP BY ID, A

then if it still fails

=[A] + Nz([C],0)

Or

=Nz([A],0) + Nz([C],0)

EDIT: Just read ajax's reply after I posted. I think his solution is the correct one

[Papa Echo]

Thank you for the replies. Ajax is correct, I needed a subquery. It works now.
And the default value of Nz([C]) is 0 if C is Null.