Select ID, DLookup("D" & Value, "MyTable", "ID=" & ID) As ValueChoice
From Mytable
Original question: http:Q_28476429.html
SELECT Top 1 Choose(ID,C011,C042,C303,C084,C005,C016,C777,C008,C009,C010,
C111,C012,C013,C014,C015,C016,C017,C018,C019,C020,
C021,C022,C023,C024,C025,C026,C027,C028) AS ValueChoice
FROM BigTable;
SELECT Top 1 Switch(ID=1,C001, ID=2,C002, ID=3,C003, ID=4,C004, ID=5,C005,
ID=6,C006, ID=7,C007, ID=8,C008, ID=9,C009, ID=10,C010,
ID=11,C011, ID=12,C012, ID=13,C013, ID=14,C014) As ValueChoice
FROM BigTable;
SELECT split("Id District County Est_Id State_Id")(0) FROM Inspections;
SELECT split("Id,District,County,Est_Id,State_Id", ",",-1,0)(0) FROM Inspections;
Public Function ABChoose(ByVal parmWhichField, ParamArray parmFields())
'A Better Choose Function
Select Case parmWhichField
Case 1 To UBound(parmFields) + 1
ABChoose = parmFields(parmWhichField - 1)
Case Else
ABChoose = Null
End Select
End Function
Since the array of fields is zero based and the first parameter of the function expects an ordinal selection value (one based), we have to adjust our bounds checking and item retrieval accordingly.
Public Function GetNthFieldName(ByVal parmValue, ByVal parmFields As String, Optional parmBase = 1)
If parmBase = 1 Then
GetNthFieldName = Split(parmFields, ",")(parmValue - 1)
Else
GetNthFieldName = Split(parmFields, ",")(parmValue)
End If
End Function
DLookup(GetNthFieldName(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)
Select ID, DLookup(GetNthFieldName(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)
From Mytable;
SELECT top 1
IIF([parmFieldnum]<=25, Choose([parmFieldnum],B.C001, B.C002, B.C003, B.C004, B.C005, B.C006, B.C007, B.C008, B.C009, B.C010, B.C011, B.C012, B.C013, B.C014, B.C015, B.C016, B.C017, B.C018, B.C019, B.C020, B.C021, B.C022, B.C023, B.C024, B.C025),
IIF([parmFieldnum]<=50, Choose([parmFieldnum]-25, B.C026, B.C027, B.C028, B.C029, B.C030, B.C031, B.C032, B.C033, B.C034, B.C035, B.C036, B.C037, B.C038, B.C039, B.C040, B.C041, B.C042, B.C043, B.C044, B.C045, B.C046, B.C047, B.C048, B.C049, B.C050)))
FROM BigTable AS B;
Therefore, we can only select up to 52 (max) columns with a non-nested IIF statement.
SELECT TOP 1
IIf([parmFieldnum]<=26,Choose([parmFieldnum],B.C001,B.C002,B.C003,B.C004,B.C005,B.C006,B.C007,B.C008,B.C009,B.C010,B.C011,B.C012,B.C013,B.C014,B.C015,B.C016,B.C017,B.C018,B.C019,B.C020,B.C021,B.C022,B.C023,B.C024,B.C025,B.C026),
Choose([parmFieldnum]-26, B.C027,B.C028,B.C029,B.C030,B.C031,B.C032,B.C033,B.C034,B.C035,B.C036,B.C037,B.C038,B.C039,B.C040,B.C041,B.C042,B.C043,B.C044,B.C045,B.C046,B.C047,B.C048,B.C049,B.C050,B.C051,B.C052)) AS ValueChoice
FROM BigTable AS B;
SELECT TOP 1 Switch([parmFieldnum]<=25,Choose([parmFieldnum],B.C001,B.C002,B.C003,B.C004,B.C005,B.C006,B.C007,B.C008,B.C009,B.C010,B.C011,B.C012,B.C013,B.C014,B.C015,B.C016,B.C017,B.C018,B.C019,B.C020,B.C021,B.C022,B.C023,B.C024,B.C025),
[parmFieldnum]<=50,Choose([parmFieldnum]-25, B.C026, B.C027,B.C028,B.C029,B.C030,B.C031,B.C032,B.C033,B.C034,B.C035,B.C036,B.C037,B.C038,B.C039,B.C040,B.C041,B.C042,B.C043,B.C044,B.C045,B.C046,B.C047,B.C048,B.C049,B.C050),
[parmFieldnum]<=75,Choose([parmFieldnum]-50, C051, C052, C053, C054, C055, C056, C057, C058, C059, C060, C061, C062, C063, C064, C065, C066, C067, C068, C069, C070, C071, C072, C073, C074, C075),
[parmFieldnum]<=100,Choose([parmFieldnum]-75, C076, C077, C078, C079, C080, C081, C082, C083, C084, C085, C086, C087, C088, C089, C090, C091, C092, C093, C094, C095, C096, C097, C098, C099, C100),
[parmFieldnum]<=125,Choose([parmFieldnum]-100, C101, C102, C103, C104, C105, C106, C107, C108, C109, C110, C111, C112, C113, C114, C115, C116, C117, C118, C119, C120, C121, C122, C123, C124, C125),
[parmFieldnum]<=150,Choose([parmFieldnum]-125, C126, C127, C128, C129, C130, C131, C132, C133, C134, C135, C136, C137, C138, C139, C140, C141, C142, C143, C144, C145, C146, C147, C148, C149, C150),
[parmFieldnum]<=175,Choose([parmFieldnum]-150, C151, C152, C153, C154, C155, C156, C157, C158, C159, C160, C161, C162, C163, C164, C165, C166, C167, C168, C169, C170, C171, C172, C173, C174, C175),
[parmFieldnum]<=200,Choose([parmFieldnum]-175, C176, C177, C178, C179, C180, C181, C182, C183, C184, C185, C186, C187, C188, C189, C190, C191, C192, C193, C194, C195, C196, C197, C198, C199, C200)
) AS ValueChoice
FROM BigTable AS B;
SELECT TOP 1 Choose((([parmFieldnum]-1)\8)+1,
Choose((([parmFieldnum]-1) mod 8)+1, C001,C002,C003,C004,C005,C006,C007,C008),
Choose((([parmFieldnum]-1) mod 8)+1, C009,C010,C011,C012,C013,C014,C015,C016),
Choose((([parmFieldnum]-1) mod 8)+1, C017,C018,C019,C020,C021,C022,C023,C024),
Choose((([parmFieldnum]-1) mod 8)+1, C025, C026 ,C027,C028,C029,C030,C031,C032),
Choose((([parmFieldnum]-1) mod 8)+1, C033,C034,C035,C036,C037,C038,C039,C040),
Choose((([parmFieldnum]-1) mod 8)+1, C041,C042,C043,C044,C045,C046,C047,C048),
Choose((([parmFieldnum]-1) mod 8)+1, C049, C050, C051, C052, C053, C054, C055, C056),
Choose((([parmFieldnum]-1) mod 8)+1, C057, C058, C059, C060, C061, C062, C063, C064 ),
Choose((([parmFieldnum]-1) mod 8)+1, C065, C066, C067, C068, C069, C070, C071, C072 ),
Choose((([parmFieldnum]-1) mod 8)+1, C073, C074, C075, C076, C077, C078, C079, C080 ),
Choose((([parmFieldnum]-1) mod 8)+1, C081, C082, C083, C084, C085, C086, C087, C088 ),
Choose((([parmFieldnum]-1) mod 8)+1, C089, C090, C091, C092, C093, C094, C095, C096 ),
Choose((([parmFieldnum]-1) mod 8)+1, C097, C098, C099, C100, C101, C102, C103, C104 ),
Choose((([parmFieldnum]-1) mod 8)+1, C105, C106, C107, C108, C109, C110, C111, C112 ),
Choose((([parmFieldnum]-1) mod 8)+1, C113, C114, C115, C116, C117, C118, C119, C120 ),
Choose((([parmFieldnum]-1) mod 8)+1, C121, C122, C123, C124, C125, C126, C127, C128 ),
Choose((([parmFieldnum]-1) mod 8)+1, C129, C130, C131, C132, C133, C134, C135, C136 ),
Choose((([parmFieldnum]-1) mod 8)+1, C137, C138, C139, C140, C141, C142, C143, C144 ),
Choose((([parmFieldnum]-1) mod 8)+1, C145, C146, C147, C148, C149, C150, C151, C152 ),
Choose((([parmFieldnum]-1) mod 8)+1, C153, C154, C155, C156, C157, C158, C159, C160 ),
Choose((([parmFieldnum]-1) mod 8)+1, C161, C162, C163, C164, C165, C166, C167, C168 ),
Choose((([parmFieldnum]-1) mod 8)+1, C169, C170, C171, C172, C173, C174, C175, C176 ),
Choose((([parmFieldnum]-1) mod 8)+1, C177, C178, C179, C180, C181, C182, C183, C184 ),
Choose((([parmFieldnum]-1) mod 8)+1, C185, C186, C187, C188, C189, C190, C191, C192 ),
Choose((([parmFieldnum]-1) mod 8)+1, C193, C194, C195, C196, C197, C198, C199, C200)
) AS ValueChoice
FROM BigTable AS B;
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (4)
Commented:
I'm just thinking that you have to be working with a really poorly normalized database to need that level of nesting just to identify the field you want to select. I believe I had to deal with that many fields, I'd write the SQL string dynamically.
Dale
Author
Commented:Commented:
Author
Commented:I posted the (much) simpler solution in the original question thread. The fact that these fields were sequentially numbered facilitated the dynamic column name creation via simple concatenation inside of a DLookup() function.