‘Not’ Query (Acc2000)

Topic

New Reply

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 ??

Reply | Quote

Replies

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

Reply | Quote

Thanks Mike, That works fine.

Dave

Reply | Quote

Thanks Mike, That works fine.

Dave

Reply | Quote

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.

Reply | Quote

Thanks Drew, Very nice

Reply | Quote

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:

In ("Paint","Panel","Fit")

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

Not In ("Paint","Panel","Fit")

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

Reply | Quote

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.

Reply | Quote

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;

Reply | Quote

Francois, once again a problem solved.

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

Reply | Quote

Francois, once again a problem solved.

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

Reply | Quote

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;

Reply | Quote

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.

Reply | Quote

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:

In ("Paint","Panel","Fit")

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

Not In ("Paint","Panel","Fit")

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

Reply | Quote

Thanks Drew, Very nice

Reply | Quote

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.

Reply | Quote