John Spencer said:One method would be
Select IIF([D]="A",[A],IIF([D]="B",,IIF([D]="C",[C],Null))) as Return
FROM YourTable
In the query grid:
Field: Return: IIF(D="A",[A],IIF(D="B",B,IIF(D="C",C,Null)))
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
I have a table that looks like:
A B C D
2 5 3 B
1 7 11 A
6 9 13 C
How do I write a query that returns the values in the field indicated in
D? The result should be:
5
1
13
Thanks!
Steve
Steve said:John, thanks for responding!
Yes, I see what you are doing. However, it gets pretty complicated when
there are over 10 fields in the table. Do you have any ideas if a subquery
could be used someway for this? Do you know of any expression that takes
the fieldname as a parameter and returns the value in that field?
Something like:
SomeExpression([C]) where ID = 3 = 13
I guess it would work similar to the HLookup and VLookup in Excel.
Steve
John Spencer said:One method would be
Select IIF([D]="A",[A],IIF([D]="B",,IIF([D]="C",[C],Null))) as Return
FROM YourTable
In the query grid:
Field: Return: IIF(D="A",[A],IIF(D="B",B,IIF(D="C",C,Null)))
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
I have a table that looks like:
A B C D
2 5 3 B
1 7 11 A
6 9 13 C
How do I write a query that returns the values in the field indicated in
D? The result should be:
5
1
13
Thanks!
Steve
John Spencer said:If the ID is a primary key, then you could use DLookup
Assumptions:
ID is the primary key (or a key candidate)
ID is a number field
Field: TheValue: DLookup([D],"TableName","ID=" & TableName.ID)
IF ID is not a primary key, then you could do it with a more complex set
of criteria if you can always uniquely identify the row.
Other Options would be to use SWITCH or CHOOSE
Choose could be used if the fieldNames were all the same length or you
make them all the same length by adding spaces to them
Assumption: all field names are one character in length
Choose(Instr(1,"ABC",D),A,B,C)
Assumptions:
Longest field name is 3 characters,
Fields are A, Bx, Crr
Choose(Instr(1,"A ,Bx ,Crr",D)\3 +1,A,Bx,Crr)
And of course, you could always write a VBA function and pass it the
source field (D) and all the other fields and have the function do the
work.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
Steve said:John, thanks for responding!
Yes, I see what you are doing. However, it gets pretty complicated when
there are over 10 fields in the table. Do you have any ideas if a
subquery could be used someway for this? Do you know of any expression
that takes the fieldname as a parameter and returns the value in that
field? Something like:
SomeExpression([C]) where ID = 3 = 13
I guess it would work similar to the HLookup and VLookup in Excel.
Steve
John Spencer said:One method would be
Select IIF([D]="A",[A],IIF([D]="B",,IIF([D]="C",[C],Null))) as Return
FROM YourTable
In the query grid:
Field: Return: IIF(D="A",[A],IIF(D="B",B,IIF(D="C",C,Null)))
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Steve wrote:
I have a table that looks like:
A B C D
2 5 3 B
1 7 11 A
6 9 13 C
How do I write a query that returns the values in the field indicated
in D? The result should be:
5
1
13
Thanks!
Steve
Steve said:Thanks again, John!
DLookup returns B in the first record, A in the second and C in the third.
Steve
John Spencer said:If the ID is a primary key, then you could use DLookup
Assumptions:
ID is the primary key (or a key candidate)
ID is a number field
Field: TheValue: DLookup([D],"TableName","ID=" & TableName.ID)
IF ID is not a primary key, then you could do it with a more complex set
of criteria if you can always uniquely identify the row.
Other Options would be to use SWITCH or CHOOSE
Choose could be used if the fieldNames were all the same length or you
make them all the same length by adding spaces to them
Assumption: all field names are one character in length
Choose(Instr(1,"ABC",D),A,B,C)
Assumptions:
Longest field name is 3 characters,
Fields are A, Bx, Crr
Choose(Instr(1,"A ,Bx ,Crr",D)\3 +1,A,Bx,Crr)
And of course, you could always write a VBA function and pass it the
source field (D) and all the other fields and have the function do the
work.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
Steve said:John, thanks for responding!
Yes, I see what you are doing. However, it gets pretty complicated when
there are over 10 fields in the table. Do you have any ideas if a
subquery could be used someway for this? Do you know of any expression
that takes the fieldname as a parameter and returns the value in that
field? Something like:
SomeExpression([C]) where ID = 3 = 13
I guess it would work similar to the HLookup and VLookup in Excel.
Steve
One method would be
Select IIF([D]="A",[A],IIF([D]="B",,IIF([D]="C",[C],Null))) as
Return
FROM YourTable
In the query grid:
Field: Return: IIF(D="A",[A],IIF(D="B",B,IIF(D="C",C,Null)))
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Steve wrote:
I have a table that looks like:
A B C D
2 5 3 B
1 7 11 A
6 9 13 C
How do I write a query that returns the values in the field indicated
in D? The result should be:
5
1
13
Thanks!
Steve