Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343

    Unanswered: SQL usage in querying Unmatched records

    Sorry guys brain dead when least expected so a guide here would be greatfully recieved

    Ive a this in SQL - basically its an Unmatched Query however, I need it to look at 2 fields and not just the one (surname). The one I need it to also check is the [lp1].[Date of]! as you see im ok up to the FROM statement but Ive just fully forgotten how to add in a second part to the statement for [lp1].[Date of]!

    SELECT [Keyskills].[ID], [Keyskills].[Assessor], [Keyskills].[First name(s)], [Keyskills].[Surname], [Keyskills].[Date of], [Keyskills].[NI Number], [Keyskills].[Trainee Start Date], [Keyskills].[TERMINATIONDAT]
    FROM Keyskills LEFT JOIN lp1 ON [Keyskills].[Surname] = [lp1].[Surname]
    WHERE ([lp1].[Surname] Is Null);

    sorry for this simple one but i lost the plot today can cant find it anywhere


    regards and thanks


    gareth

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    AND nextField IS NULL
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Thanks for the quick reply teddy - looked at it and wonder should i have placed an AND in there? like so *

    SELECT Keyskills.ID, Keyskills.Assessor, Keyskills.[First name(s)], Keyskills.Surname, Keyskills.[Date of], Keyskills.[NI Number], Keyskills.[Trainee Start Date], Keyskills.TERMINATIONDAT
    *FROM Keyskills LEFT JOIN lp1 ON (Keyskills.[Date of] = lp1.[Date of]) AND (Keyskills.Surname = lp1.Surname)*
    WHERE (((lp1.Surname) Is Null));

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Provided access supports that, yeah that's a decent way to go about it. I'm more of a t-sql guy myself so I can't say for sure, give it a shot.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Many thanks for that teddy - nice to have the support

    regards


    gareth

Posting Permissions

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