Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Question Unanswered: Finding dates and Nulls

    Hi

    I am trying to write a query so that I can get data from a table containing absence dates. There are 2 tables - a name table and an absence date table.

    What I need to do is to list all of the names from the absence table with any dates for which they are absent over a specified range of dates, but if a person is not absent for that range of dates, then the result set still needs to list that person's name once.

    I just can't think how to do this. I can't seem to exclude the name from the name table if there is a value returned from the absence table.

    Does anyone have any ideas?

    Thanks
    Make something idiot proof and someone will make a better idiot...

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try using an outer join.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    Thanks, but that's what I've been trying. Let me try to clarify the problem with a few rules:

    1. I need to show any dates for which an employee is absent.
    2. If the employee has no absences during the period, then I need to show that they were in the office.
    3. If an employee has an entry in the absence table, then I do not need a record to show that they were in the office.
    4. Results should be listed my second name, alphabetically, so there will be either a single row if the person has no absences, or one or more rows with absence dates.

    If I join the tables or use a union query I don't seem to be able to get a result set that contains only the absence date(s) or a row that shows they were in the office.
    Make something idiot proof and someone will make a better idiot...

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Have you tried anything yet?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    union join and outer join are two different things. Read up on them. It will help.
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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