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?