Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    4

    Unanswered: Select query with two criteria

    Consider the table:

    Name Date Amount
    1 John 1/1/2009 120
    2 Chris 1/7/2009 45
    3 Mary 1/2/2009 35
    4 John 1/18/2009 100
    5 Chris 1/1/2009 20

    I need to select all records except those where name is John or Chris and date is 1/1/2009. So I should retrieve only records 2,3 and 4. What SQL should I use?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Name 
         , Date 
         , Amount
      FROM daTable
     WHERE NOT
           ( Name IN ('John','Chris') 
         AND Date = #1/1/2009#
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Unless of course Name gives you trouble, then just put it in square brackets:

    [Name]

    Same with Date. Both of these are reserved words.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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