I'm trying to list ALL offices in a district but return results for a specific course. The query I currently have works somewhat, but if I select First Aid and office A has records for First Aid and MS Word, it returns two results for that office.
Code:
SELECT tblOffices.OfficeName, IIf([CourseID]<>138 Or IsNull([CourseID])=True,0,[AllottedSpots]) AS [Allotted Spots], tblOffices.ID
FROM tblOffices LEFT JOIN tblOfficeAllottedSpots ON tblOffices.ID = tblOfficeAllottedSpots.OfficeID
WHERE (((tblOfficeAllottedSpots.CourseID)=138 Or (tblOfficeAllottedSpots.CourseID) Is Null) AND ((tblOffices.District)=54)) OR (((tblOfficeAllottedSpots.CourseID)<>138 And (tblOfficeAllottedSpots.CourseID) Is Not Null) AND ((tblOffices.District)=54))
ORDER BY tblOffices.OfficeName;
It's very close, but not perfect.
Thanks,
Scott