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

[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.
--- As if this reposting, I have done some slogging, but it is getting
complicated, so I thought I would ask again.
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.]

 

[John Vinson]

Well, this is NOT at all easy, since it's not at all a relational
structure. It's a very nice spreadsheet, but the operations you are
describing are archetypical spreadsheet operations and do not at all
lend themselves to SQL queries!

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

Would it be possible to recast this data (perhaps using a "normalizing
union query") into a tall-thin format such as

ChFToOptF R Point
-1 R1 1.0
-1 Rlp25 1.25
-1 Rlp5 1.5
-1 R2 2
1 R1 1
1 Rlp25 1
....<etc>

With this more normalized design you can very easily find the row
(searching for the value of CHFtoOptF) and column (searching for the
value of R) and find the corresponding point. THere is no need to
think of "across" or "down" - just use the values as indexes into a
table and go directly to the desired record.

 

[plh]

Hello John,
Thank you for your reply. What is a "normalizing union query"? The "tall-thin"
format you describe sounds promising.
Thanks Again,
-plh

Well, this is NOT at all easy, since it's not at all a relational
structure. It's a very nice spreadsheet, but the operations you are
describing are archetypical spreadsheet operations and do not at all
lend themselves to SQL queries!


Would it be possible to recast this data (perhaps using a "normalizing
union query") into a tall-thin format such as

ChFToOptF R Point
-1 R1 1.0
-1 Rlp25 1.25
-1 Rlp5 1.5
-1 R2 2
1 R1 1
1 Rlp25 1
...<etc>

With this more normalized design you can very easily find the row
(searching for the value of CHFtoOptF) and column (searching for the
value of R) and find the corresponding point. THere is no need to
think of "across" or "down" - just use the values as indexes into a
table and go directly to the desired record.

I keep hitting "Esc", but I'm still here!
(If "123" is in the email address that's and anti-spam thing - remove it)

 

[John Vinson]

Hello John,
Thank you for your reply. What is a "normalizing union query"? The "tall-thin"
format you describe sounds promising.
Thanks Again,
-plh

It's a way to get data out of a "wide-flat" non-normalized table like
yours into proper form. You'll need to go to the SQL window in a new
Query to create it; the SQL would be something like

SELECT ChFToOptF, "R1" AS R, [R1] AS Point FROM yourtable
UNION ALL
SELECT ChFToOptF, "R1p25" AS R, [R1p25] AS Point FROM yourtable
UNION ALL
SELECT ChFToOptF, "R1p5" AS R, [R1p5] AS Point FROM yourtable
UNION ALL
SELECT ChFToOptF, "R2" AS R, [R2] AS Point FROM yourtable

Save this Query and then base a new MakeTable query on it.