Is there a (reasonably) simple way to get the intersection of a row and a column?

[plh]

Access 97:
Hopefully little table this will display OK for everyone who looks at
it. There are some tabs & spaces.

ChFToOptF R1 R1p25 R1p5 R2
-1 1 1.25 1.5 2
1 1 1 1 1
0.9 1 1.06 1.09 1.14
0.8 1 1.12 1.19 1.31
0.7 1 1.18 1.3 1.5

Anyway what I want to do is look across the first row, the one that
starts with -1, until I see the value that I want, say, 1.5, then look
down that column until I say the one that corresponds to (say) a
ChFToOptF value of 0.9. In that case the answer would be 1.09.
Once I've mastered that then I also want to be able to do the same for
2X2 arrays, say the intersection of ChFToOptF 0.8 and 0.7 with the
columns that correspond to 1.5 and 2 in row 1, so the return would be
1.19 1.31
1.3 1.5
for purposes of interpolation. But that comes later if I can find a
way to do the simple version. I tried various cross tab queries but
none return anything. I seems like there should be a simple way to do
it of which I am ignorant, so I thought I would ask before slogging
through a lot of Recordset Clones.
In case it makes a difference, the table is linked to an Excel
spreadsheet (Also 97).
Thank You,
-plh


I keep hitting "Esc" -- but I'm still here!
[if "123" is in email address, that is an anti-spam thing.]

 

[Guest]

It sounds like you should be able to get what you want with regular queries and specific criteria

My suggestion is build a form to run the queries from. Add CommandButtons for the different queries and have ComboBoxes for specifying the different criteria. Then reference the ComboBoxes in the queries as the criteria



----- plh wrote: ----

Access 97
Hopefully little table this will display OK for everyone who looks a
it. There are some tabs & spaces.

ChFToOptF R1 R1p25 R1p5 R
-1 1 1.25 1.5
1 1 1 1
0.9 1 1.06 1.09 1.1
0.8 1 1.12 1.19 1.3
0.7 1 1.18 1.3 1.

Anyway what I want to do is look across the first row, the one tha
starts with -1, until I see the value that I want, say, 1.5, then loo
down that column until I say the one that corresponds to (say)
ChFToOptF value of 0.9. In that case the answer would be 1.09.
Once I've mastered that then I also want to be able to do the same fo
2X2 arrays, say the intersection of ChFToOptF 0.8 and 0.7 with th
columns that correspond to 1.5 and 2 in row 1, so the return would be
1.19 1.3
1.3 1.
for purposes of interpolation. But that comes later if I can find
way to do the simple version. I tried various cross tab queries bu
none return anything. I seems like there should be a simple way to d
it of which I am ignorant, so I thought I would ask before sloggin
through a lot of Recordset Clones
In case it makes a difference, the table is linked to an Exce
spreadsheet (Also 97)
Thank You
-pl


I keep hitting "Esc" -- but I'm still here
[if "123" is in email address, that is an anti-spam thing.

 

[plh]

Hello Howard,
Thank you for your reply.
Like I said I tried that sort of query-of-a-query, and I get nothing.
It is important to me to be able to locate the field (column)
numerically by looking across the row that begins -1 until I find a
value, say 1.5, then look down the field (column) to find the one that
is across from (say) 0.9.
It is easy to return the two rows
ChFToOptF R1 R1p25 R1p5 R2
-1 1 1.25 1.5 2
0.9 1 1.06 1.09 1.14
with
SELECT *
FROM ltblVAvg_VOpt
WHERE (((ltblVAvg_VOpt.ChFToOptF)=0.9 Or
(ltblVAvg_VOpt.ChFToOptF)=-1));
but then how to locate the value 1.09, which is "down" from 1.5? That
is, the intersection of the 2nd row and column with the value sought
(1.5), in he first row. That is the question.
Thanx,
-plh

It sounds like you should be able to get what you want with regular queries and specific criteria.

My suggestion is build a form to run the queries from. Add CommandButtons for the different queries and have ComboBoxes for specifying the different criteria. Then reference the ComboBoxes in the queries as the criteria.




----- plh wrote: -----

Access 97:
Hopefully little table this will display OK for everyone who looks at
it. There are some tabs & spaces.

ChFToOptF R1 R1p25 R1p5 R2
-1 1 1.25 1.5 2
1 1 1 1 1
0.9 1 1.06 1.09 1.14
0.8 1 1.12 1.19 1.31
0.7 1 1.18 1.3 1.5

Anyway what I want to do is look across the first row, the one that
starts with -1, until I see the value that I want, say, 1.5, then look
down that column until I say the one that corresponds to (say) a
ChFToOptF value of 0.9. In that case the answer would be 1.09.
Once I've mastered that then I also want to be able to do the same for
2X2 arrays, say the intersection of ChFToOptF 0.8 and 0.7 with the
columns that correspond to 1.5 and 2 in row 1, so the return would be
1.19 1.31
1.3 1.5
for purposes of interpolation. But that comes later if I can find a
way to do the simple version. I tried various cross tab queries but
none return anything. I seems like there should be a simple way to do
it of which I am ignorant, so I thought I would ask before slogging
through a lot of Recordset Clones.
In case it makes a difference, the table is linked to an Excel
spreadsheet (Also 97).
Thank You,
-plh


I keep hitting "Esc" -- but I'm still here!
[if "123" is in email address, that is an anti-spam thing.]

I keep hitting "Esc" -- but I'm still here!
[if "123" is in email address, that is an anti-spam thing.]