Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Unanswered: Using NOT EXISTS or NOT IN or EXCEPT to limit recordset

    I am creating an application (Win XP, Access 2003 & ASP/VB) for an insurance company that sells to truckers. On the 15th of each month, the system will send an e-mail to all the clients reminding them to log in and check their account info. When they log in, we capture their ID and a timestamp. Five days later, the system will send a reminder e-mail to everyone who has NOT logged in.

    My problem is, how do I write a SQL Query that will tell me who has NOT logged in?

    Relevant Tables:

    Code:
    Client 
       ClientID
       ClientBusinessName
    
    MonthlyStatus
       MSID
       MS_ClientID
    
    The ClientID and MS_ClientID are linked.
    
    This SQL Query gets me a list of everyone who HAS logged in.  
    
    SELECT MontlyStatus.*, Client.*
    FROM MontlyStatus, Client
    WHERE MontlyStatus.MS_ClientID = Client.ClientID
    ORDER BY ClientBusinessName ASC
    How do I write a query that shows me who has NOT logged in?

    This doesn’t work:
    Code:
    SELECT MontlyStatus.*, Client.ClientID
    FROM MontlyStatus, Client
    WHERE MontlyStatus.MS_ClientID <> Client.ClientID
    ORDER BY ClientBusinessName ASC
    And neither does this:

    Code:
    SELECT MontlyStatus.*, Client.*
    FROM MontlyStatus, Client
    WHERE NOT EXISTS
     (SELECT MontlyStatus.*, Client.ClientID
    FROM MontlyStatus, Client
    WHERE MontlyStatus.MS_ClientID = Client.ClientID)
    ORDER BY ClientBusinessName ASC

    As always, any and all help would be greatly appreciated.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have a go with LEFT JOIN, you may also want to peek at the Unmatched Records wizard.
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85
    Thanks for the tip. I also looked it up in the Visual Quickstart Guide by Chris Fehily and finally got it to work (I actually tried a LEFT JOIN earlier today and couldn't get it to work).

    Code:
    SELECT DISTINCT c.*
    FROM Client c
    LEFT OUTER JOIN MontlyStatus m
    ON c.ClientID = m.MS_ClientID
    WHERE m.MS_ClientID IS NULL
    ORDER BY ClientBusinessName ASC
    NOTE: the c and m are, of course, aliases for the full table name. It's just some shorthand to make things easier to read.

Posting Permissions

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