Access SQL - How to get a second column from table field built on row source value list type?-CodePudding

I created a simple table - table1. There are two field [table1].[id] and [table1].[metod].

[table1].[id] is a key field.
[table1].[metod] has

RowSourceType - 'Value list'

and

Row Source is ' 1;"A";35;"B";2;"C";3;"D" ' (so two columns)

I'm looking for solution how to make query with a second column from [table1].[metod] field. For example for table

id	metod
1	35
2	2
3	1

I would like to receive result:

id	metod
1	B
2	C
3	A

Thank You in advance. rgds

CodePudding user response：

You can use Switch:

Select 
    id, 
    Switch([method]=1,"A",[method]=35,"B",[method]=2,"C",[method]=3,"D") As MethodCode
From
    table1

CodePudding user response：

As @Gustav suggested and perhaps you didn't understand, first step is to create a lookup table, second step is to use that as your RowSource, third step is to build your query

Benefits of this approach is that you don't need to change your Rowsource every time you make a change to the Lookup List

1. Create Lookup Table

2-a. Change Rowsource of your input field

2-b Set up columns and Hide the ID field

3. Build your final query

SELECT Table1.ID, LookupTable.LookupValue
FROM Table1 INNER JOIN LookupTable ON Table1.metod = LookupTable.LookupID;

Results of Query