An inventory summary may need to show every location, including locations holding no items. A left join preserves those empty locations, but counting every joined row can then give an empty location a misleading count of one.

Use a disposable Access example with a Locations table and an Items table. Give Location 12 two items with distinct, non-null ItemID values, and Location 24 no items. Each item refers to its location through LocationID.

Count the identity of the child

Microsoft documents that a LEFT JOIN retains all records from its left table, including those without a matching right-hand record. Count(*) counts result rows even when fields are Null, while Count(field) excludes Null values in the chosen field. For a child count, choose a child key that is present for every actual child record.

SELECT Locations.LocationID,
       Count(Items.ItemID) AS ItemCount
FROM Locations LEFT JOIN Items
ON Locations.LocationID = Items.LocationID
GROUP BY Locations.LocationID;

The intended result is Location 12 with two items and Location 24 with zero. In the unmatched left-join row, there is no child ItemID to count. Replacing the expression with Count(*) would count that preserved row, answering a different question.

Choose a field that cannot disappear on a real child

Do not count an optional description merely because it is visible in the report. A real item with a Null description could then be omitted from the count. The child's actual non-null identifier is a better expression of what is being counted.

Inspect both locations in the small result and compare them with the child table. Include an item whose optional description is missing to prove that the chosen count still includes it.

When applying the query to real data, inspect additional joins and filters. They can multiply child matches or remove empty parents before aggregation; a correct Count expression does not repair an unintended source set.

Keep the query's meaning beside the report: every location is retained, and each count represents matched item identities. Reconcile a zero, a single-item location and a multi-item location before relying on the summary for an inventory decision.

Sources: Microsoft: Access Count; Microsoft: Access outer joins.