Query not returning all expected records

[keith2511]

I have a simple database of two tables - parents and children.

I have written a query with a type 2 join so that all parents and related children are returned.

However the results omitted two parents that did not have any children.

What have I done wrong and what should I have done?

Thanks

 SELECT etr.Name, etr.[Organisation Code], etr.[National Grouping], ets.Name, ets.[Organisation Code], ets.[Close Date], ets.HospitalFROM etr LEFT JOIN ets ON etr.[Organisation Code] = ets.[Parent Organisation Code]
WHERE (((etr.[National Grouping])="y62") AND ((ets.[Close Date]) Is Null) AND ((ets.Hospital)=True) AND ((etr.[Close Date]) Is Null))
ORDER BY etr.Name;

[ranman256]

You need an outer join.
Dbl-click the join line,
Set it to show all records in the master,
some recs in child.

[keith2511]

Thanks ranman, but isn't that what I've done?

[ranman256]

and it did not show null records in the child table?

did you bring down a child record field(s)?

[Gicu]

I think in the ets.hospital field you need to add Or Is Null to the True in the criteria row.

Cheers,

[keith2511]

Thank you Gicu, I'll give that a go