Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73

    Unanswered: Query Join is making me mad

    I have two tables that I need to get info from.

    - tblPersonnel has [name] and [SSN] (relationship one) fields
    -- tblEvals (subtable) has the [SSN] (relationship many) and [EvaluationDate]

    I need a query to select all of the tblPersonnel records but then only the records in the tblEvals where there is no [EvaluationDate] or no [EvaluationDate] before a certian date.

    > Problem is there are no records in tblEvals so EvaluationDate is null don't work!

    > Not sure if I explained this correctly, but any input is appreciated.

    Peter

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Please could you post the SQL?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the join clause

    id expect it to be something like....
    Code:
    select P.Name,P.SSN, EvaluationDate from tblPesonnel as P
    Left Join on P.SSN = TblEvals.SSN
    where  isnull(EvaluationDate)=TRUE or EvaluationDate<#mytargetdate#
    order by Name
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Many moons ago I tested
    isnull(EvaluationDate)=TRUE
    VS
    EvaluationDate IS NULL

    The former was much less efficient.

  5. #5
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73

    Check out ths SQL

    > I need to look in Evaluations table and select those who have not had a quarterly evaluation!

    I want to select all records in Table Personnel (Left Join)
    - Records in Evaluations where no evaluation date is >4/1/2008


    SELECT tblPPersonnel.LastName, tblPPersonnel.FirstName, tblPEvals.EvalDate
    FROM tblPPersonnel LEFT JOIN tblPEvals ON tblPPersonnel.SSN = tblPEvals.SSN
    WHERE (((((tblPEvals.EvalDate)<#4/1/2008#) Or Not (tblPEvals.EvalDate)>#4/1/2008# Or (tblPEvals.EvalDate) Is Null)));

    Thanks for helping
    Peter

Posting Permissions

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