Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2016
    Posts
    4

    Unanswered: Remove rows based on multiple fields

    I am looking to exclude a specific item from my query that is based on multiple fields. For example if I have a database of cars, greatly larger than shown below:

    Make | Color

    Ford | Red
    Toyota | Blue
    Chevy | Green
    Ford | Black

    I can't get Access to exclude only red and blue Fords.

    My criteria is: Make: Not Like "Ford" | Color: Not Like "Red" And Not Like "Blue"

    When I run the query it excludes all red and blue cars.

    Any help is greatly appreciated! Thank you!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    And the actual sql is?

    My expectation is that you are ORring the make and colour criteria

    Rather than use the like costruct you coukd use in

    Where make <> 'FORD' and colour Not in ('BLUE', 'RED')
    Last edited by healdem; 08-25-16 at 19:00.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2016
    Posts
    4

    Actual SQL

    SELECT [Test Two 1 of 2].[Contact ID], [Test Two 1 of 2].Age, [Test Two 1 of 2].[Email Permission], [Test Two 1 of 2].[Hold Sample Order], [Test Two 1 of 2].[Contact Status], [Test Two 1 of 2].[Primary Business Area], [Test Two 1 of 2].Date, [OppReport for US users].CAMPAIGN_NAME, [OppReport for US users].SALES_STAGE
    FROM [Test Two 1 of 2] LEFT JOIN [OppReport for US users] ON [Test Two 1 of 2].[Contact ID] = [OppReport for US users].CONTACT_ID

    WHERE ((([OppReport for US users].CAMPAIGN_NAME)<>"Brava Ring") AND (([OppReport for US users].SALES_STAGE) Not Like "*captured*" And ([OppReport for US users].SALES_STAGE) Not Like "*confirmed*")) OR ((([OppReport for US users].CAMPAIGN_NAME) Is Null) AND (([OppReport for US users].SALES_STAGE) Is Null));

    This is the actual SQL. Of course I don't have a database of cars, but I want to exclude sales stages of captured or confirmed that have a campaign name of Brava Ring. Of course there are other campaign names and sales stages.

    I used the car example as it is easier to understand.

    Thank you for your help, I have been trying lots of variations but nothing works.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    WHERE (CAMPAIGN_NAME<>"Brava Ring" AND (SALES_STAGE Not Like "*captured*" And SALES_STAGE Not Like "*confirmed*" );

    you don't need to test for campaign_name to be null as by definition NULL is not equal to "brava ring"
    nor do you don't to test for sales_stage to be null as by definition NULL is not like "*captured*" OR "*CONFIRMED*"

    Because you've changed the rules Im not too certain if it should be
    WHERE (CAMPAIGN_NAME<>"Brava Ring" OR (SALES_STAGE Not Like "*captured*" And SALES_STAGE Not Like "*confirmed*" );
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2016
    Posts
    4

    Continued Problems

    I tried the solution you provided, however, Access excluded everything that has a "Sales Stage" of Captured or Confirmed with the criteria of: Not Like "*captured*" And Not Like "*confirmed*". Any ideas?

    Click image for larger version. 

Name:	2016-09-06 13_10_26-Access -.png 
Views:	2 
Size:	3.8 KB 
ID:	17099

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by k_haj View Post
    ....Any ideas?

    Click image for larger version. 

Name:	2016-09-06 13_10_26-Access -.png 
Views:	2 
Size:	3.8 KB 
ID:	17099
    Yes use SQL not the query designer
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2016
    Posts
    4

    Thank You

    Quote Originally Posted by healdem View Post
    Yes use SQL not the query designer
    This worked! Using the SQL and not the query designer was the way to go! Thank you!

Tags for this Thread

Posting Permissions

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