MS Access Query Help

[coucar3456]

I have a query with field name COMPNOUN, is there a way, I can have all engines come back with number 1 and all transmission come back with number 7. Below is an example of some of the values listed in the field.

COMPNOUN
6.5 Engine
Transfer Case
Engine
5.7L Engine
Transmission
Radio

 

[Micron]

Probably a UNION query, but your post doesn't make much sense because I can't evaluate your request against the data sample. That is, I see no "engine with number 1" or "transmission with number 7", although I do see a value that contains 7. If the data and problem described is far removed from what is actually going on, it sends us down the wrong path. I only mention this because I've been seeing more of this approach lately.
If you are not familiar with UNION queries, they are easy to learn how to create them. Gotta say though that if that's a memo field with all those values, or a multi-value field, or even just a sample of one field but with several records, you are in for a tough road ahead due to the design approach.

 

[Steven Schoors]

This field in your Query could do the job:
NameToYourChoice: IIf([COMPNOUN] Like "*Engine*";1;IIf([COMPNOUN] Like "*Transmission*";7;0))
The result looks then like:

 

[coucar3456]

Thanks for your help.