Append Query/ New Column Query

[Gootz11]

I'm trying run a query that would assign a value to a record in a new column. I have a column call Row, which shows the row where my item is located. example

Item Row
1111 CA
2222 GA
3333 SA

I want my new column to look at my "ROW" column and assign a value depending on the letters

example:
CA = E1E
GA = E1D
SA = E1G

so my new query would look like this

Item Row Location
1111 CA E1E
2222 GA E1D
3333 SA E1G

I don't even know if this is possible, but any help you can provide would be truly appreciated
Thanks

 

[pdx_man]

Two solutions...
1) Create a table with two fields (Row, Location)with the proper mapping

Tbl_Map
Row Location
CA E1E
GA E1D
SA E1G

Now, add this table to your query joining them by the Row fields. Add Location field to your query.

2) Use an IIF statement:
In a blank column in the design view of your query

Location: IIF ([row]="CA","E1E",IIF([row]="GA","E1D",IIF([row]="SA","E1G","No Location")))

The first one in preferred because it is more scaleable if you require additional mappings or need to edit existing ones.

HTH

 

[Gootz11]

PDX_Man
Thanks, It worked Out Great!