Date Count Function

[Vaporhead]

Hello.

I am trying to create a query that counts the amount of injuries each one of my store locations have had for a certain date period. If a location has not had an injury, then I want it to return a zero value. If I add my date field [DOI] from [tblmishaps] with any sort of criteria entered, it seems to return only results that have this criteria and ignores my RIGHT JOIN.

Tables:
"Locations"
"tblMishaps"

SELECT Locations.[Location Name], Count(tblMishaps.ID) AS [Total Mishaps]
FROM tblMishaps RIGHT JOIN Locations ON tblMishaps.[Store Location] = Locations.[Location Name]
WHERE (((tblMishaps.DOI) Between #4/1/2023# And #4/1/2024#))
GROUP BY Locations.[Location Name];

[June7]

It's not ignored, just joined at wrong point. Build aggregate query with filter then join the aggregate query to Locations.

[Vaporhead]

Not sure why I didn't think of that. Thanks.