Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Not' Query (Acc2000)

    I have a table with a list of Item Descriptions.

    They could be :

    Paint, Panel, Fit, Other, Fuel, Valet, etc

    I need a query to extract all possible names other than Paint, Panel, Fit.
    The below PNG is showing how I thought I should base the criteria in the query, but it only works for the first line ie (Not "Paint")

    How should I correctly construct the query criteria ??

  2. #2
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Not' Query (Acc2000)

    Dave:

    You query is checking for records where ([Item Descriptions] <>"Paint") or ([Item Descriptions] <>"Panel") or ([Item Descriptions] <>"Fit")

    You need to link the criteria with an AND.

    On one row in the grid put the following under the Item Description field:

    <>"Paint" AND <>"Panel" AND <> "Fit"

    -Mike

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Not' Query (Acc2000)

    Thanks Mike, That works fine.


    Dave

  4. #4
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Not' Query (Acc2000)

    Another train of thought is to use a Left Join, and look for null values. This would also allow you to increase the number of items you want to 'ignore', because there is a limit of how many ANDs you can use in a query. I am attaching a sample database (Access 97) which has tblPRT and tblDescriptions. There is one query, with the following SQL:

    SELECT SUP_NO, ITM_DES, QTY
    FROM tblPRT LEFT JOIN tblDescriptions ON tblPRT.ITM_DES=tblDescriptions.Description
    WHERE Description Is Null

    That SQL is telling JET to pull all records from tblPRT where the matching description in tblDescriptions.Description is Null (or, in english, there is no matching record in tblDescriptions). To add or remove values that you want to ignore, simply add or remove records from tblDescriptions.

    Just another way of looking at things.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Not' Query (Acc2000)

    Thanks Drew, Very nice <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: 'Not' Query (Acc2000)

    Another option is to use the SQL IN operator. According to Jet SQL Help, the IN operator "determines whether the value of an expression is equal to any of several values in a specified list." And: "If expr is found in the list of values, the In operator returns True; otherwise, it returns False. You can include the Not logical operator to evaluate the opposite condition (that is, whether expr is not in the list of values)." For example, this statement, used in WHERE clause:

    <code>In ("Paint","Panel","Fit")</code>

    will select all records where the Description equals Paint, Panel, or Fit. This statement:

    <code>Not In ("Paint","Panel","Fit")</code>

    will select all records where the Description does NOT equal Paint, Panel, or Fit. This is simpler than stringing together a long string of = or <> expressions. The IN operator tends to be pretty efficient, but NOT IN, like NOT LIKE and other "NOT" expressions, will be somewhat less efficient. I am not aware of any upper limit on the number of items you can use in an IN expression; I've used more than a hundred (in programmatically-created SQL statements) w/o experiencing problems or errors. The upper limit on the number of ANDs in an Access query's WHERE or HAVING clause is 40 (in A2K). In addition, you are limited to a maximum of 1024 characters in a cell in the query design grid; this would not apply to an SQL statement generated in code.

    HTH

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Not' Query (Acc2000)

    I can't get this query to work.

    I need to create this query so I can link it to another query.
    I need to Sum all the figures in ALL_HRS and TAK_HRS so I have one grouped EST_NO & SUP_NO.

    It needs to read:

    [18245]-[0]-[15.52]-[125.91] without the GRP_CDE.

    I took the tick out of the GRP_CDE box hoping it would group and sum correctly, but still shows individuals.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Not' Query (Acc2000)

    Dave, Is this the query you want ?
    SELECT tblESTLAB.EST_NO, tblESTLAB.SUP_NO, Sum(tblESTLAB.ALL_HRS) AS SumOfALL_HRS, Sum(tblESTLAB.TAK_HRS) AS SumOfTAK_HRS
    FROM tblESTLAB
    WHERE (((tblESTLAB.GRP_CDE)<>"paint" And (tblESTLAB.GRP_CDE)<>"Panel" And (tblESTLAB.GRP_CDE)<>"Fit" And (tblESTLAB.GRP_CDE)<>"Mech" And (tblESTLAB.GRP_CDE)<>"Recep" And (tblESTLAB.GRP_CDE)<>"Mat"))
    GROUP BY tblESTLAB.EST_NO, tblESTLAB.SUP_NO;
    Francois

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Not' Query (Acc2000)

    Francois, once again a problem solved.

    I must appologise for my in ability to fathom out queries which seem to be so easy.

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

Posting Permissions

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