Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    20

    Unanswered: Scheduling Query

    I need help......

    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
    FROM Table1
    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.........

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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).
    Paul

  3. #3
    Join Date
    Oct 2009
    Posts
    340
    I tend to agree w/ Baldy as you are trying to twist sql around an illogical construct (no offense intended)

    you are recording vacation dates - thus your query should be 'who is on vacation'

    what good is the return of Larry & Mo 2009 data for a 2010 inquiry? very messy in large quantities

    Curly 3/15 is the record you want returned - he is the guy on vacation.

    Now if you want to get sexy you can write another query based on the 1st query and make a record set of everyone that is Not Curly....

    but fundamentally you need to start with selecting who is on vacation.

  4. #4
    Join Date
    Jan 2010
    Posts
    20
    PBaldy and NTC - THANK YOU! Don't know WHY this direction didn't occur to me to begin with BUT......I tend to like to do things the hard way!

    Creating the "Unmatched" query works like a dream!

    Thank you again!

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problemo, glad to help.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •