Chris Pfeiffer
asked on
LEFT JOIN Access Query
I have two tables tblMEMBERS (m) and tblMEMBERSDUTYSTATUS (d) structured as seen below:
tblMembers (m)
I have the following:
I need it to check for Nulls on a specific date in the DutyDate column and when I try the below query it returns 0 and I have sorted it myself and know their are values to return.
tblMembers (m)
ID Office OfficeTemp Element ElementTemp
1 10 NULL 1 NULL
2 10 NULL 2 NULL
3 8 NULL 3 NULL
4 10 NULL 4 NULL
5 7 NULL 1 NULL
6 10 NULL 2 NULL
tblMembersDutyStatus (d)ID Member DutyStatus DutyDate
1 1 14 12/12/2016
2 1 14 12/13/2016
3 3 9 12/12/2016
4 1 14 12/14/2016
5 1 2 12/15/2016
6 1 2 12/16/2016
7 6 11 12/12/2016
8 5 14 12/12/2016
9 2 12 12/12/2016
10 3 14 12/12/2016
I am trying to get the query to pull all the names on tblMembers (m) whose Office = 10 (or OfficeTemp =10) and Element = 1 (or Element = 1) and does not have a listing on tblMembersDutyStatus (d) in the DutyDay column. The Element portion I can added later once I figure out whats going on with the date option.I have the following:
SELECT Count(m.ID) AS PFD
FROM tblMembers AS m LEFT JOIN tblMemberDutyStatus AS d ON m.ID = d.Member
WHERE (((m.Office)=10) AND ((d.Member) Is Null)) OR (((d.Member) Is Null) AND ((m.OfficeTemp)=10))
This gives me the names of everyone on the tblMembers with the Office or OfficeTemp of 10 who at NO time has had a value on the tblMambersDutyStatus.I need it to check for Nulls on a specific date in the DutyDate column and when I try the below query it returns 0 and I have sorted it myself and know their are values to return.
SELECT Count(m.ID) AS PFD
FROM tblMembers AS m LEFT JOIN tblMemberDutyStatus AS d ON m.ID = d.Member
WHERE (d.dutydate =#12/12/2016# AND ((m.Office)=10) AND ((d.Member) Is Null)) OR (((d.Member) Is Null) AND ((m.OfficeTemp)=10))
A little guidance would be great.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Upload a sample database, and indicate the required output.
Add the date reqirement into the join conditions, that way only the relevant rows from tblMemberDutyStatus are considered
SELECT Count(m.ID) AS PFD
FROM tblMembers AS m
LEFT JOIN tblMemberDutyStatus AS d ON m.ID = d.Member and d.dutydate =#12/12/2016#
WHERE m.Office=10
AND m.OfficeTemp=10
AND d.Member IS NULL
ASKER
Okay thanks for the suggestions. I tried both of them and did got the desired results from Shane's Post.
*PortletPaul from what I found you cannot have an AND statement on the JOIN clause in Access.
I only changed the WHERE clause:
WHERE ((m.Office = 11 AND m.Element = 2 AND m.OfficeTemp IS Null) OR (m.OfficeTemp=11 and m.ElementTemp=2)) AND
This allowed for looking at both Office and Element along with their Temp Value as well as ignoring the nonTemp values if they are assigned to a temp office.
Thank you so much for the help!
*PortletPaul from what I found you cannot have an AND statement on the JOIN clause in Access.
I only changed the WHERE clause:
WHERE ((m.Office = 11 AND m.Element = 2 AND m.OfficeTemp IS Null) OR (m.OfficeTemp=11 and m.ElementTemp=2)) AND
This allowed for looking at both Office and Element along with their Temp Value as well as ignoring the nonTemp values if they are assigned to a temp office.
Thank you so much for the help!