Extract String from Text Field

[LadyDi]

In my database, I have a field that contains a "Status Byte". The status byte contains various pieces of information about a service call, on of which is a four character code (any combination of either one letter and three numbers or two letters and two numbers) for what is wrong. I have all the possible codes listed in a separate table and was wondering if there is a way to write a query that will pull these codes from the text fields. In most cases, the code is the first thing mentioned in the Status Byte. However, there are a few times when the code is mentioned in the middle.

In other words, is it possible to have a query look in a text field and find a four character combination that is listed in a separate table?

Any assistance you can provide would be greatly appreciated.

 

[plog]

The status byte contains various pieces of information about a service call

Why? Distinct data needs to be in its own field. Can you provide some sample data and identify the codes within them?

 

[jdraw]

If you have a table with a design along these lines

tblErrorCodes
errorCd PK number
ErrorName text
ErrorDescription text

and
tblServiceCall
ServicecallId PK
other info related to Service Call


If one service call can have 1 or more ErrorCodes
then a table such as

tblServicecallErrors
ServicecallErrorsId PK
Servicecallid FK to tblServicecall
ErrorID FK to tblErrorCodes
... other fields specific to this Servicecall and this Error code

This allows you to have consistent Errorcodes and Descriptions. and
multiple errorcodes per service call.

Easy (query) to get error descriptions from ErrorId

 

[LadyDi]

My problem is arising, because the data in the database is acutally just imported from a couple different sources. We have two programs that track our service calls and we are trying to combine them in this database. One source uses the error code and one source uses the status byte. Neither source contains a comprehensive list of service calls so I created an outer join query to view them all. So, each table does have distinct data, I just want to merge them together.

The codes that I am looking for in the Status Byte are things like E605 or CE11. Sometimes the Status Byte begins with those codes and sometimes it begins with the unit type (i.e. ENA CE11). In other cases, the Status Byte begins with the cause of the problem and then the error code (i.e. Vandalism E605). I just need a way to pull the error code out of this field. Relationships between these tables are set up on outer joins based on the call numbers.

 

[jdraw]

A few questions while reading your post.

"The data is imported from a couple of sources -- I want to merge them together" Does that mean these sources will be dropped/removed when you build the database? Or, what exactly?

If the sources continue to exist, won't you be going through the import and error code/status byte manipulation forever?

In the big scheme of things are Error Codes and Status Bytes the very same thing? That is, if they are merged are we still talking apples or (apples and oranges)?

You should be able to find which codes are single entity/entry (CE11, E605..) and those that contain spaces (multiple entity/entry ENA CE11). That would seem to separate the task into groups for review. Are you planning on changing the (ENA CE11) ? Will the sources of such code be changed as well (or discontinued)?

 

[LadyDi]

No, the two separate sources are not going away. What I am working on is a special project to help improve our products. Once the products are improved, the database will no longer be used.

When comparing the error codes (either in a separate column or in the status byte) you are comparing apples to apples. The error code means the same thing no matter were it is found.

All I want out of the status bytes are the error codes. I do not care about the rest of the information in that field at this time.

 

[jdraw]

So this data base is really a tool to "sort out" whatever issues there are with the current error codes/status bytes?
It isn't clear, to me, how all this fits together.
Can you give a very high level description of the situation you are trying to resolve in plain English and business terms (not Access or error code queries)?