Link to home
Start Free TrialLog in
Avatar of Chris Pfeiffer
Chris PfeifferFlag for Japan

asked on

LEFT JOIN Access Query

I have two tables tblMEMBERS (m) and tblMEMBERSDUTYSTATUS (d) structured as seen below:

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

Open in new window

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

Open in new window

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))

Open in new window

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))

Open in new window

A little guidance would be great.
ASKER CERTIFIED SOLUTION
Avatar of Shane Krueger
Shane Krueger

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Hamed Nasr
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

Open in new window

Avatar of Chris Pfeiffer

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!