Expression to determine Schedule availability 1

[dbar10]

I have a query that searches a worker's general availability as stated in the availability table. This is when worker's are available to work. then I link that to the schedule table. I want to see what workers are not already scheduled during a particular time frame that day.
The first query is: BaseAvailqry:

SELECT [Worker table].WorkerID, [Worker table].WorkStatusID, [Worker table].LastName, [Worker table].FirstName, [Worker table].Middle, [Worker table].CityID, [Worker table].Phone1, [Job Description].JobDesc, [Job Description].State, Availability.DaysID, Availability.Begin, Availability.End
FROM ([Job Description] INNER JOIN [Worker table] ON [Job Description].WorkerID = [Worker table].WorkerID) INNER JOIN Availability ON [Worker table].WorkerID = Availability.WorkerID
WHERE ((([Worker table].WorkStatusID)=1) AND (([Worker table].CityID)=[Forms]![SchedSearchList]![ComboCity]) AND (([Job Description].JobDesc)=[Forms]![SchedSearchList]![ComboJob]) AND (([Job Description].State)=[Forms]![SchedSearchList]![State]) AND ((Availability.DaysID)=[Forms]![SchedSearchList]![ComboDay]) AND ((Availability.Begin)>=[Forms]![SchedSearchList]![StartTime]) AND ((Availability.End)<=[Forms]![SchedSearchList]![EndTime]));

Then I link that to SchedAvailqry:

SELECT BaseAvailqry.WorkerID, BaseAvailqry.WorkStatusID, BaseAvailqry.LastName, BaseAvailqry.FirstName, BaseAvailqry.Middle, BaseAvailqry.CityID, BaseAvailqry.Phone1, BaseAvailqry.JobDesc, BaseAvailqry.State, Schedule1.Date, Schedule1.Day, Schedule1.Start, Schedule1.End
FROM Schedule1 INNER JOIN BaseAvailqry ON Schedule1.WorkerID = BaseAvailqry.WorkerID
WHERE (((Schedule1.Date)<>[Forms]![SchedSearchList]![Date]) AND ((Schedule1.Day)<>[Forms]![SchedSearchList]![ComboDay]));

This is a mindbender for me. What expression can I use to put in the Start and End fields to return people who are not scheduled during this time?

Any help would be appreciated, Thanks

 

[dbar10]

Sorry had an error in BaseAvailqry, have corrected below:

SELECT [Worker table].WorkerID, [Worker table].WorkStatusID, [Worker table].LastName, [Worker table].FirstName, [Worker table].Middle, [Worker table].CityID, [Worker table].Phone1, [Job Description].JobDesc, [Job Description].State, Availability.DaysID, Availability.Begin, Availability.End
FROM ([Job Description] INNER JOIN [Worker table] ON [Job Description].WorkerID = [Worker table].WorkerID) INNER JOIN Availability ON [Worker table].WorkerID = Availability.WorkerID
WHERE ((([Worker table].WorkStatusID)=1) AND (([Worker table].CityID)=[Forms]![SchedSearchList]![ComboCity]) AND (([Job Description].JobDesc)=[Forms]![SchedSearchList]![ComboJob]) AND (([Job Description].State)=[Forms]![SchedSearchList]![State]) AND ((Availability.DaysID)=[Forms]![SchedSearchList]![ComboDay]) AND ((Availability.Begin)<=[Forms]![SchedSearchList]![StartTime] And (Availability.Begin)<=[Forms]![SchedSearchList]![EndTime]) AND ((Availability.End)>=[Forms]![SchedSearchList]![StartTime] And (Availability.End)>=[Forms]![SchedSearchList]![EndTime]));

 

[dbar10]

I figured it out!!!
Just in case anyone else may need this:

BaseAvailqry:

SELECT [Worker table].WorkerID, [Worker table].WorkStatusID, [Worker table].LastName, [Worker table].FirstName, [Worker table].Middle, [Worker table].CityID, [Worker table].Phone1, [Job Description].JobDesc, [Job Description].State, Availability.DaysID, Availability.Begin, Availability.End
FROM ([Job Description] INNER JOIN [Worker table] ON [Job Description].WorkerID = [Worker table].WorkerID) INNER JOIN Availability ON [Worker table].WorkerID = Availability.WorkerID
WHERE ((([Worker table].WorkStatusID)=1) AND (([Worker table].CityID) Like ("*" & [Forms]![SchedSearchList]![ComboCity])) AND (([Job Description].JobDesc)=[Forms]![SchedSearchList]![ComboJob]) AND (([Job Description].State)=[Forms]![SchedSearchList]![State]) AND ((Availability.DaysID)=[Forms]![SchedSearchList]![ComboDay]) AND ((Availability.Begin)<=[Forms]![SchedSearchList]![StartTime] And (Availability.Begin)<=[Forms]![SchedSearchList]![EndTime]) AND ((Availability.End)>=[Forms]![SchedSearchList]![StartTime] And (Availability.End)>=[Forms]![SchedSearchList]![EndTime]));

SchedAvailqry:

SELECT BaseAvailqry.WorkerID, BaseAvailqry.WorkStatusID, BaseAvailqry.LastName, BaseAvailqry.FirstName, BaseAvailqry.Middle, BaseAvailqry.CityID, BaseAvailqry.Phone1, BaseAvailqry.JobDesc, BaseAvailqry.State, Schedule1.Date, Schedule1.Day, Schedule1.Start, Schedule1.End
FROM Schedule1 RIGHT JOIN BaseAvailqry ON Schedule1.WorkerID = BaseAvailqry.WorkerID
WHERE (((Schedule1.Date) Is Null Or (Schedule1.Date)=[Forms]![SchedSearchList]![Date]) AND ((Schedule1.Start) Is Null Or (Schedule1.Start)<=[Forms]![SchedSearchList]![StartTime] And (Schedule1.Start)<=[Forms]![SchedSearchList]![EndTime] Or (Schedule1.Start)>=[Forms]![SchedSearchList]![StartTime] And (Schedule1.Start)>=[Forms]![SchedSearchList]![EndTime]) AND ((Schedule1.End) Is Null Or (Schedule1.End)<=[Forms]![SchedSearchList]![StartTime] And (Schedule1.End)<=[Forms]![SchedSearchList]![EndTime] Or (Schedule1.End)>=[Forms]![SchedSearchList]![StartTime] And (Schedule1.End)>=[Forms]![SchedSearchList]![EndTime]));

That was fun.

 

[lespaul]

have a star for figuring it out AND posting your solution!

Leslie

Come join me at New Mexico Linux Fest!

 

[dbar10]

Thank you