Change Field Text

[cindyredo]

Hi there. I am attempting to change the output in a query field. Meaning: if the equipment code=x,y or z, I need the query to print a,b or c.

I know I have done this in the past but cannot remember how nor can I find it in help or my book.

Any help would be appreciated.

Thanks.

 

[Pat Hartman]

Create a conversion table. Two columns - OldCode and NewCode. Create a query that joins to the OldCode field and select the NewCode field for output.

 

[cindyredo]

Hello and thank you for your response. I thought it was something like that and I did try that earlier. Just so I'm sure I understand:

create the new table with only two columns; then create a query that contains both the new table and the original data table as well?

Thanks.

 

[cindyredo]

Another Snag

Hi again. Ok, I have the table that makes the conversion, however this is how it is structured (I have duplicates)

Old Code New Code
H001 ABC
H002 DEF
H003 GHI
C001 ABC
C002 DEF
C003 GHI

You see that 2 different old codes need to have the same new code, so when I make the new query I am getting multiple records on the same data -- any way to suppress??

Thanks alot. I really appreciate this!!

 

[Pat Hartman]

It is imperative that you have uniqueness on the old code to properly convert the data. If one of the old codes can convert to more than one of the new codes, there must be some other piece of information involved in the conversion. Otherwise how would you decide which of the new codes to assign. If the second new code is for future use, you can remove that code from the conversion table so that you have uniqueness on the old code.