select field from same record

Microsoft Access 2010

I have a table with a number of fields and I want to create a query which selects a field from the current record based on the value of another field in the query.   Here is a simplified example

Table is 5 fields:  ID, D1,D2, D3,Value

I want a query to return 2 fields

ID, result     where if Value =1 result is D1, if value =2 result is D2 etc.

There are actually 31 fields top select from so a nested iif is too complex

Any suggestion on how to do this

I cannot change the table as it is an ODBC link from a corporate database
LVL 1
isenseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy PooleCommented:
You can try a switch statement:
Select ID,Switch(Value=1,D1,Value=2,D2,Value=3,D3) As V from yourtable

Open in new window

add as many as you need
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
I think the Choose() function would be simpler and quicker (better performing.
Example:
Select ID, Choose(Value, D1, D2, D3, D4, D5, D6, D7, D8, D9, D10,
D11, D12, D13, D14, D15, D16, D17, D18, D19, D20,
D21, D22, D23, D24, D25, D26, D27, D28, D29, D30, D31) As ValueChoice
From Mytable

Open in new window

0
Randy PooleCommented:
There you go, a couple examples to Choose from, pun intended :P
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aikimarkCommented:
chuckle. lulz.
0
isenseAuthor Commented:
Thanks for your suggestions guys.

I initially chose to choose choose!!!!!!!

However, Access reported it as too complex when I entered all 31 cases.  So, I configured inside an iif (For value less than 26) with choose for the first 25 options and then switch for the remainder (as with switch I can start with a value of 26 and go on to 31).

So, I needed to use both your suggestions in combination and have accepted them both

Thanks again for your help

Martyn
0
aikimarkCommented:
I didn't realize that the CHOOSE() function has a 29 parameter limit.  Even though this problem is closed (solved), here is another approach that has no limits.

1. Create a string splitting function

Although you can use the SPLIT() function in your Access query, you can't address individual elements of the resultant array.
Public Function Q_28476429(ByVal parmValue, ByVal parmFields As String, Optional parmBase = 1)
    If parmBase = 1 Then
        Q_28476429 = Split(parmFields, ",")(parmValue - 1)
    Else
        Q_28476429 = Split(parmFields, ",")(parmValue)
    End If
End Function

Open in new window

Note: The optional parmBase parameter determines whether we are referencing the SPLIT() function result in an ordinal manner (1st, 2nd, 3rd, etc.) or whether as an absolution position (base zero).

2. Invoke the new function as the first parameter of a DLookup() function

The DLookup() function has three parameters.

1. Field/Column name
2. Table/query name
3. Row selection criteria
In this example, we are going after the 12 field in the "Inspections" table where the ID value is the value of our current row's ID field.
dlookup(Q_28476429(12,"Id,District,County,Est_Id,State_Id,Request_Number,Ehs_Id,Ehs,Territory,Est_Type,Insp_Type,Insp_Date,Extra_Credit,Grade,Score_SUM,Final_Score_SUM,Seats,Inspection_Time_Hrs,Inspection_Time_Min,Sample,Setup_Date,Update_Date,Update_User_Id,Water,Sewage,Origin,Permit_Status_Id,Permit_Status"),"Inspections","ID=" & ID) As ValueChoice

Open in new window

Using this as your solution

Instead of Choose(), Switch(), or a hybrid using IIF(), we can now do this.
Select ID, DLookup(Q_28476429(Value, "D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31"), 
"MyTable", "ID=" & ID) As ValueChoice
From Mytable
FROM Inspections;

Open in new window

0
aikimarkCommented:
I am writing an article on this.  I'll post a link when it is published.
0
aikimarkCommented:
Even though the article is just about finished, I wanted to post the simplest solution for you.  In this case, I'm taking advantage of the fact that your fields/columns have digit-suffixed  names.
Select ID, DLookup("D" & Value, "MyTable", "ID=" & ID) As ValueChoice
From Mytable

Open in new window

0
isenseAuthor Commented:
That's neat - I like it
0
aikimarkCommented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.