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

    Unanswered: SQL Query help: who has NOT logged in this month?

    I'm writing a system (ASP/VB with Access 2003) for an insurance company that requires clients to log in at least once a month. The information MUST be stored for historical reasons (it also tracks what changes they made during the month).

    I need to show who HAS logged in this month and who has NOT logged in this month.

    ----------------------

    Code:
    The Tables involved are Client and Monthly Status
    
    Client 
    ClientID
    ClientBusinessName
    ClientIsActive     y/n
    etc.
    
    MonthlyStatus
    MSID
    MS_ClientID
    MSCertificationDate
    MSHasChanges     y/n
    MSChangeNotes
    
    The ClientID and MS_ClientID are linked.
    It's easy to show who has logged in (using varMonth and varYear variables as filters):
    Code:
    SELECT MonthlyStatus.*, Client.* 
    FROM MonthlyStatus, Client 
    WHERE Client.ClientID = MonthlyStatus.MS_ClientID 
    AND ( Month(MonthlyStatus.MSCertificationDate) = " & varMonth & " 
    AND Year(MonthlyStatus.MSCertificationDate) = " & varYear & " ) 
    ORDER BY ClientBusinessName ASC
    And I've even figgured out how to show who HAS NOT logged in, but I can't figure out how to add a date filter:
    Code:
    SELECT c.* 
    FROM Client c  
    LEFT OUTER JOIN MonthlyStatus m  ON c.ClientID = m.MS_ClientID 
    WHERE m.MS_ClientID IS NULL  
    ORDER BY ClientBusinessName ASC
    I'm thinking it might need some sort of subquery, but I'm not sure.
    Any ideas?

  2. #2
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    I would use "not exists", selecting your clients that did log in.
    Last edited by vich; 10-10-06 at 19:50. Reason: need to test

  3. #3
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85
    vich: Does Access support NOT EXISTS? I'm pretty sure it doesn't...

    If it does, could you provide me with a sample of the correct syntax in this instance?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, access supports [NOT] EXISTS
    Quote Originally Posted by buzzter66
    ... but I can't figure out how to add a date filter
    add the condition to the ON clause --
    Code:
    SELECT c.* 
      FROM Client c  
    LEFT OUTER 
      JOIN MonthlyStatus m  
        ON (
           c.ClientID = m.MS_ClientID 
       AND Month(m.MSCertificationDate) = " & varMonth & " 
       AND Year(m.MSCertificationDate) = " & varYear & " 
           ) 
     WHERE m.MS_ClientID IS NULL  
    ORDER 
        BY c.ClientBusinessName ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by buzzter66
    vich: Does Access support NOT EXISTS? I'm pretty sure it doesn't...

    If it does, could you provide me with a sample of the correct syntax in this instance?
    Sorry; have to go somewhere and no time to build your tables.

    In general; look up "Create a subquery (ADP)" in the help. It explains the "Exists" and "IN" clauses. Probably the "IN" clause would be even more appropriate for your application.

    For the Northwind database, here's a quick sample of a query that excludes all the "A" customers


    Code:
    SELECT Customers.CustomerID, Customers.CompanyName
    FROM Customers 
    where not exists (select * from Customers cust  where Customers.CustomerID = Cust.CustomerID and  left(cust.CustomerID,1)  = "A");

Posting Permissions

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