Extract a number from a table using coordinates

[Silvio]

Hello folks, a have a table similar to the one below:

100 200 300
1 10 11 12
2 25 28 30
3 50 56 59

Please note the left column is a column-head and the top raw is the
heading-raw (both, the column and the heading are numeric and so is the data.
How can I extract data based on coordinate from the column and the head-raw?
For example, the data for 2:200 would be 28 the data for 3:100 would be 50
and so forth (just like the battleship game. I have a form that will contain
numbers which will match one of the column numbers (1, 2, 3 etc.) and I have
a second field that will contain a number which will match the raw-head (100,
200, 300 etc.) the third field will be the number from the coordinates from
the table. ). I never done this before and I have no idea where to start.

Thanks,
Silvio

 

[Douglas J. Steele]

I hate to be the bearer of bad news, but that table is not properly designed
for a relational database.

Instead of what you've shown, it should be:

Column Row Field3
1 100 10
1 200 11
1 300 12
2 100 25
2 200 28
2 300 30
3 100 50
3 200 56
3 300 59


That way, you'd simply query:

SELECT Field3
FROM MyTable
WHERE Column = 1
AND Row = 100

If you're stuck with the design, you can create a Union query that
normalizes the data, and then query that query.

If we assume that your field names are ColumnHead, 100, 200 and 300, your
Union query would be something like:

SELECT ColumnHead AS Column, 100 AS Row, [100] AS Field3
FROM MyTable
UNION
SELECT ColumnHead, 200 , [200]
FROM MyTable
UNION
SELECT ColumnHead, 300, [300]
FROM MyTable

 

[Dale Fye]

Another way to deal with this misguided data structure would be to create a
function that would return a value. Something like:

Public Function GetValue(RowHeader as long, ColHeader as long) as Variant

Dim strCriteria as string
Dim rs as DAO.recordset

strSQL = "SELECT [" & ColHeader & "] FROM yourTable " _
& "WHERE [LeftColumnName] = " & RowHeader
set rs = currentdb.openrecordset strsql, dbfailonerror

if rs.eof then
GetValue = NULL
else
GetValue = rs(0)
endif
rs.close
set rs = nothing

Exit Function

You will have to change the name of your [LeftColumnName] to the name of
your leftmost column, but this should do the job.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.