asked on

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

ASKER CERTIFIED SOLUTION

Randy Poole

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Randy Poole

There you go, a couple examples to Choose from, pun intended :P

aikimark

chuckle. lulz.

Martyn Kenyon

ASKER

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

aikimark

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

aikimark

I am writing an article on this. I'll post a link when it is published.

aikimark

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

Martyn Kenyon

ASKER

That's neat - I like it

aikimark

Here is the article link:
http:A_14179-Big-Choices-Dynamic-columns-with-A-Better-Choose-function.html