Substitution of one value for another in a SELECT query (how to do it) 1

[startup]

I have a series of values in my table. Each record only has one value, but they fall in these number sets:

256-260
512-516
768-772
1024-1028
1280-1284

Now each of these sets has five values, and I figured out how to convert all the numbers into a value of 0-4 and display this in my SELECT query (ie 256 = 0, 257 = 1, 512 = 0, 513 = 1, etc)

I am stumped on the next step though which is that I would like to display the word "Excellent" if the number = 0, "Very Good" if it is a 1, and so on down to "Awful" if it is a 4. Is there anyway to do this in my Select Query. I am not wanting to update or change the value in this field just to display it in this format when the query is viewed. I thought about creating another table with 0-4 in one column and the names in the 2nd, but I couldn't figure out how to link the two tables then. I would prefer not to have to have another table anyway if there is an easier way to do this.

Thank you,
ricomundy

 

[dhookom]

How did you "I figured out how to convert all the numbers into a value of 0-4"? I would use another table. You could try an expression like:
=Choose([YourNumber]\256 ,"Exellent","Very Good","...")

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[startup]

Thank you Duane for the perfect answer! The "Choose" function was exactly what I needed and I had never knew about it. I can't use another table because it is sort of a proprietary database that I'm just hooking into to view the things I want to.

Anyway to explain about these numbers (256) actually contains information for two different values in it. The fact that it is in the 200 range means one thing and the fact that 256 is the beginning of the range of (256-260) means something else. Here are the final formulas I came up with to extract the data in 2 different columns. Maybe there is an easier way to do the Erating formula, but it works for me.

Elevel: Choose(([Rating]\256),"Very Energetic","Lively","Fair","Lacking","None")


Erating: Choose([Rating]-(256*(Format(Left(([Rating])/256,1))))+1,"Excellent","Very Good","Good","Average","Awful")

 

[dhookom]

I would have to view the data to determine a better expression. It seems this number is a decimal representation of a binary number.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]