Help with Pattern matching in access query

[javajockey]

I am creating a database to analyze data and create common groups. In my database I have to create pattern match strings using the "LIKE" operator to search for text. For example, if a field contains "DEV" then I need access to print "DEVELOPER" in a new column. Is there a way to have the pattern matching criteria contained in a table so I can extend the search criteria and easily add additional criteria? For example, "PROD" will print "PRODUCTION" and "QA" wil print "QUALITY ASSURANCE".

[June7]

Maybe. What do you mean by 'extend the search criteria'? Do you want to search for multiple parameters on the same field?

[javajockey]

I'm pretty sure that Microsoft Access can do what I want, I'm not not sure what the name is. I have a table with over 13,000 rows. One column contains a long string of text with NON-ROOT process on UNIX. I need to create a query that will identify what the NON-ROOT process is. Over time, I will add additional processes that I will search for.

Here is an example of a text string of a non-root process that I have to pull information from.

ps852 17718 1 0 Jul12 ? 00:03:23 BBL -C dom=PLMPROD_145010 -g 30002 -i 0 -u CTPSELM01Pv -U /u01/psoft852/PLMPROD/appserv/PLMPROD/LOGS/TUXLOG -m 0 -A

The strong "PLM" in PLMPROD_145010 tells me this is a PeopleSoft Enterprise Learning Management server.


Here is another example.

ps851 5308608 1 0 Aug 03 - 0:19 BBL -C dom=PFSPROD_50749 -g 30002 -i 0 -u jblade03a08 -U /u01/psoft851/PFSPROD/appserv/PFSPROD/LOGS/TUXLOG -m 0 -A

The string "PFS" tells me that this is a PeopleSoft Finance server.

I probably will have about 50 different search criteria like this I will have to search through with more added over time.

Currently I am creating individual queries for all the various search criteria. This is going to get out of control real quick. I feel like there must be a way that I can have the search criteria in a table so I can easily add additional criteria rather an create more queries.

Thanks for you help.

[June7]

What you really need to do is extract this value from the string. Will it ALWAYS be 3 characters following = sign? Use string manipulation functions to pull the value.

Mid([fieldname], InStr([fieldname],"=")+1,3)

However, earlier you mentioned "PROD" and "QA" as examples. Consistency in structure is critical to this approach. Otherwise, any solution gets complicated fast.