[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

select field from same record

Posted on 2014-07-15
10
Medium Priority
?
515 Views
Last Modified: 2014-07-26
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
0
Comment
Question by:isense
  • 6
  • 2
  • 2
10 Comments
 
LVL 21

Accepted Solution

by:
Randy Poole earned 1000 total points
ID: 40197620
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
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 1000 total points
ID: 40197726
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
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40197734
There you go, a couple examples to Choose from, pun intended :P
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 46

Expert Comment

by:aikimark
ID: 40197736
chuckle. lulz.
0
 
LVL 1

Author Closing Comment

by:isense
ID: 40201987
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40202254
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40202879
I am writing an article on this.  I'll post a link when it is published.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40203695
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
 
LVL 1

Author Comment

by:isense
ID: 40204018
That's neat - I like it
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40221811
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question