I am creating a report in Access that will give a list of people available for work within a date range. I have the query working to eliminate those not available BUT I need it to filter even further and eliminate those on multiple assignments where the dates overlap.
Data in the table is:
InstructorID InstructorName VacBegF VacEndT
1 Larry 9/1/2009 9/30/2009
2 Mo 11/7/2009 12/7/2009
3 Curly 12/15/2009 1/15/2010
3 Curly 3/15/2010 4/15/2010
I want to know who is NOT on vacation from 3/6 - 4/1. My query is pulling the correct info by DATE but I don't know how to make it eliminate the NAME - the result gives Larry, Mo and Curly (12/15 - 1/15 record) but it should only return Larry and Mo since Curly is not available for part of the time.
This is the query:
SELECT Table1.InstructorID, Table1.InstructorName, Table1.VacBegF, Table1.VacEndT, table1.vacbegf & " - " & table1.vacendt AS Date_Range
WHERE (((([Table1].[VacEndT]>=[Beg]) And ([Table1].[VacBegF]<[End]))=False));
How do I get the query to eliminate Curly all together based on the dates input? Is this even possible in SQL?
Thank you in advance for ANY hint, thought, pointer.........
My first thought is to come at it from the other direction. I'd have a query returning those that were on vacation during the specified period, and then a second query using a frustrated join of the table of people against that first query (think unmatched query wizard).