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];