create a new column in a query

[Delboy14]

Access XP
I have a query where I would like to set the values of a column depending on values in another table.
The other table reads like this example:
Colour
Blue
Blue
Red
Yellow
Green
Yellow

I would like to create a column in a table which would assign the values 0-3 to each colour, 0-Blue, 1-Red, 2-Yellow, 3-Green.

Does anyone know how to do this?

 

[mymou]

Could do an ugly IIF statement.

But if this is just a reference table then you probably just need an extra column (to your reference colour table) and then you can use links between tables (reference and data table) to do the rest.

Easier?


Stew