Query Group By Criteria

  • Thread starter pushrodengine via AccessMonster.com
  • Start date
P

pushrodengine via AccessMonster.com

I need to query to show all records except records that include the text
"Code 1†and “Code 4†and “Code 5†in "Details" feild.
 
K

Ken Snell \(MVP\)

SELECT *
FROM TableName
WHERE [Details] <> 'Code 1' AND
[Details] <> 'Code 4' AND
[Details] <> 'Code 5';
 
J

John W. Vinson

I need to query to show all records except records that include the text
"Code 1” and “Code 4” and “Code 5” in "Details" feild.
Click to expand...

Ken's answer will give you the desired result if that's ALL that's in the
Details field. If you want to exclude records where the Details field includes
these strings along with other text (e.g. "This item deals with a Code 4
issue") then you may have a problem: will there be any Code 10 or Code 42
records?

If not... a criterion on Details of

NOT LIKE "*Code [145]*"

will work.

If you want to exclude "This is a Code 1 item" and "This item is Code 1" but
DO want to see "This item is Code 10", you'll need a dual criterion:

NOT LIKE "*Code [145] *" AND NOT LIKE "*Code [145]"


John W. Vinson [MVP]
 
P

pushrodengine via AccessMonster.com

Prefect, Thanks. I need something else that is similar.

Show all records except records that have the text "Code 8â€, “Code 11â€, “Dry
Run - Canceled Enrouteâ€, "Dry Run - UTL Incident", "Public Assist" and
"Station Coverage" in "CallDetails" feild.
 
J

John W. Vinson

Show all records except records that have the text "Code 8”, “Code 11”, “Dry
Run - Canceled Enroute”, "Dry Run - UTL Incident", "Public Assist" and
"Station Coverage" in "CallDetails" feild.
Click to expand...

The need to do this suggests that your table design needs work. Rather than
embedding what appears to be a CallType field in a (free form? typed in?) text
field - where the user could type "Dry run; UTL Incident" or "Public Asisst"
(causing the record to be miscategorized), I'd really suggest that you need a
CallCode field separate from the CallDetails field. This could use a combo box
based on a CallCodes table with the various types of call, including these.

You can DO this by adapting my suggested code... but it will be inefficient,
all but impossible to maintain, and error prone. Don't do it!

John W. Vinson [MVP]
 
P

pushrodengine via AccessMonster.com

The user must select from a Combo box only. They can't enter just anything.
So, "Code 8â€, “Code 11â€, “Dry Run - Canceled Enrouteâ€, "Dry Run - UTL
Incident", "Public Assist" and "Station Coverage" are specific selections
made by the user.
I need the query to single out all of the records that do not have "Code 8â€,
“Code 11â€, “Dry Run - Canceled Enrouteâ€, "Dry Run - UTL Incident", "Public
Assist" and "Station Coverage" in CallDetails.
 
J

John W. Vinson

I need the query to single out all of the records that do not have "Code 8”,
“Code 11”, “Dry Run - Canceled Enroute”, "Dry Run - UTL Incident", "Public
Assist" and "Station Coverage" in CallDetails.
Click to expand...

In that case a criterion of

NOT IN("Code 8”, “Code 11”, “Dry Run - Canceled Enroute”, "Dry Run - UTL
Incident", "Public Assist", "Station Coverage")

will do the job. I thought you meant that CallDetails was a larger field
containing (or not) one of these as a substring... sorry for the
misinterpretation!


John W. Vinson [MVP]
 
Top