Can I change text data to numeric data to create charts?

[Guest]

I want to give letters fixed values to enable me to creat graphs for trafic
light reports

Red = 10
Amber = 5
Green = 0

Red, Amber and Green are stored in my table against results from testing a
procedure. Red = high risk, Amber = medium and Green = low risk.
I need to show charts that assess the risk of the tested procedures

How can I assign the above values? Can a query be created to do this or am I
into programming arrays?

 

[Guest]

I would create a table to holds two fields
Color Number
Red 10
Amber 5
Green 0

Then join the new table to the old table using the color name, and
displaying the number.
I rather tis method then using IIf or Switch in the query, so incase you
need to change the number, you wont need to look for the queries you used
them in, all you have to do, is change the value in the table, and it will
automatically repair all the queries you used it in.

 

[Guest]

Thanks for the info. The problem I have is that my company has 200 shops
divided into divisions which are audited and given R, A, G grades for each
section

Unit Num Name Division DateOfVisit Banking Float Income H&S Sec
Pay
300 Bury North 01/01/06 R G
A A G G

i Would like to create charts based on the Division and the score allocated
to R, A or G. Without manually updating R to 10 which I do in Excel at the
moment. I hope you can help.

 

[Guest]

Create a query, based on the table, and add another field using the Swith
function

Select TableName.* , switch([FieldName]= "Red",10,[FieldName] = "Amber" , 5
, [FieldName] = "Green",0) As FieldNum From TableName

Now, you have a new field that display numbers for the color