Query Group By Criteria

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200801/1

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

--

Ken Snell
MS ACCESS MVP


"pushrodengine via AccessMonster.com" u36225@uwe wrote in message
news:7dd2aeff1e7b3@uwe...
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200801/1

On Sun, 06 Jan 2008 22:08:58 GMT, "pushrodengine via AccessMonster.com"
u36225@uwe wrote:

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.

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]

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.

--
Message posted via http://www.accessmonster.com

On Mon, 07 Jan 2008 01:23:01 GMT, "pushrodengine via AccessMonster.com"
u36225@uwe wrote:

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.

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]

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.

--
Message posted via http://www.accessmonster.com

On Mon, 07 Jan 2008 01:51:32 GMT, "pushrodengine via AccessMonster.com"
u36225@uwe wrote:

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.

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]

Great!
Works like a Charm!

Thank You.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200801/1