Find records that DON'T have a record in another table

[johnfrani]

I am trying to create this query as part of an Access app.
I am familiar with SQL but I am stumped by this query.

I have two tables:

tbl_Member
MemberID (pk) autonumber
Lname (text)
WatchOffGroup (text)


Example Data
MemberID Lname WatchOffGroup
1 Smith 1
2 Jones 2
3 Green 3

*******************************

tbl_Dates
DateID (pk) autonumber
Date (date/time)
DOW (text)
GroupOff (text) there is one record for every WatchOffGroup
that is scheduled OFF WORK on each date in tbl_Date.

Example Data
DateID Date DOW GroupOff
1 11/4/05 Fri 1
2 11/5/05 Sat 1
3 11/5/05 Sat 2
4 11/6/05 Sun 1
5 11/6/05 Sun 2
6 11/7/05 Mon 2
7 11/7/05 Mon 3
8 11/8/05 Tues 3
9 11/8/05 Tues 1

****************************************************

On Fri 11/4/05 Smith (WatchOffGroup 1) is off work and both
Jones (WatchOffGroup 2) and Green (WatchOffGroup 3) are
working. Neither Jones nor Green's Watch Off Group is
listed in the tbl_Dates for 11/4/05.


tbl_Member has basic employee info: last name and the
Watch Off Group they belong to. The members work a
rotating schedule - having different days off each week.


tbl_Dates has the date info: the calendar date
(ex. 11/5/05), the Date of Week (Sat) and a record for
each Watch Off Group that is scheduled not to work on
that day.


I want a query that will produce a list of member's
names who are NOT OFF on the selected date
(the ones that ARE working - their Watch Off Groups
are NOT listed in the tbl_Dates).


So, (in English) I want to
select Last name from the Member's table
when that member's Watch Off Group is NOT
listed in the Dates table
on the date I choose.

I already have the tbl_Dates that holds the Watch Off
data. I know how to add the date parameter and
I don't want to have to create a second Watch ON table -
in the real table there would be 14 Watch ON Groups for each
day of the year - and records would have to be created
for the next few years.

 

[PHV]

Something like this ?
SELECT M.MemberID, M.Lname, M.WatchOffGroup
FROM tbl_Member As M
WHERE M.WatchOffGroup Not In (SELECT GroupOff FROM tbl_Dates WHERE [Date] = [your date parameter here])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[johnfrani]

Thanks, PHV - it works.
Is it possible to change the SQL to also select the Day of Week (DOW) field from tbl_Dates?

 

[PHV]

SELECT M.MemberID, M.Lname, M.WatchOffGroup, Format([your date parameter here], 'ddd') As DOW
FROM tbl_Member As M
WHERE M.WatchOffGroup Not In (SELECT GroupOff FROM tbl_Dates WHERE [Date] = [your date parameter here])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[johnfrani]

I'm sorry. I guess I misstated what I was trying to get.
I would like to end up with these fields in the query result...

Date
DOW
Lname
WatchOffGroup

for members who are working (their watch off groups are not records for the date I select) when I input a date (ex. 11/5/2005).

I believe there must be some kind of JOIN to link tbl_Member with tbl_Dates to allow me to include the Date and DOW fields from tbl_Dates.

Thanks for your help.

 

[johnfrani]

I found a solution to my problem...

SELECT DISTINCT tbl_Member.Lname, tbl_Member.WatchOff, tbl_Dates.Date, tbl_Dates.DOW
FROM tbl_Dates ,tbl_Member
WHERE tbl_Dates.date= [Enter OT Date]
and not exists
(select 1 from tbl_Dates a
where tbl_Member.WatchOff = a.WatchOff
and a.Date=[Enter OT Date]);

John