Selecting unique field

[tallenwa]

I have a query that identifies a row and column, but can not figure out how to select the intersect of these in a joined table. Say the first two fields in my query retrieve II and c. One of the joined tables in my query is:
a b c d e f
I 1 2 3 4 5 6
II 21 22 23 24 25 26
III 31 32 33 34 35 36

If the first field in the query equals II and the second field equals d, how to I write the next field so it will return 24? Can I set a variable for the column heading? I have 20 columns that I need to apply this to, so I'm hoping I can do it without a huge nested IIF or Select statement.

Thanks.

 

[LittleSmudge]

I think you need something along the lines off

"SELECT " & TopLetter & " FROM tblName WHERE FirstColumnName = " & ColumnValue


Top letter being the single char string that is the a, b, c etc
and ColumnValue being I, II, III etc.


'ope-that-'elps.

G LS

 

[tallenwa]

Can you elaborate on what you mean by "Topletter being the single char string that is the a,b,c "

If my table is called "test", and my first column has the header MMI and the first two fields of my query are called "Row" (corresponding to the I, II, III and "Column" (Corresponding to MMI,a,b,c,d..), would I write for my next field in the query: "Exp: (SELECT [Column] FROM[test] WHERE [MMI]=[Row]"
I tried that and it didn't bring back the value.

Thanks.

 

[LittleSmudge]

You are close but you have to keep the Column and Row outside of the quotes so that they get evaluated on the fly.


"SELECT " & Column & " FROM test WHERE MMI = " & Row



G LS

 

[tallenwa]

GLS, I appreciate your patience with my syntax.
When I enter in the query field=> Expr1: "SELECT" & [Column] & " FROM test WHERE [MMI] = " & [Row]
I get a resulting text string that reads "Select c FROM test Where MMI = II"

In the query field, doesn't the "" make it treat it as text?

Thanks for your help,
T.

 

[LittleSmudge]

Yes tallenwa, I think you've got what you need.

If Row really does contain roman numberals ( text ) then you MIGHT need to put single quotes around the II
Expr1: "SELECT" & [Column] & " FROM test WHERE [MMI] = '" & [Row] & "'"



G LS