SELECT in a table like an x and y coordinate system

which database system are you using?
which version / edition of SQL server ?

does your table represent a spreadsheet / matric?

if so why not have your table normalised as

x  , y,  value, formulae?

what do you intend to mean by FINDROW ?

basically you'd either need to use dynamic sql and feed in the column name you wish to retrieve
and the selection criteria
or you'd need to have a case statement in the select clause to determine and return the deisred column...


e.g. select case @CN when "CN1" then CN1
                  when "CN2" then CN2 ....
                      end
   from yourtable
  where CN1 = @rownumber


you've asked this question several times but have never really advised us what the true background is ....

please give us a fuller description.examples of what the intention is..

I use an Access database. But I thought the SQL statement is the same.

conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ClassLibrary1/db.mdb");

The table represents a spreadsheet made by a wizard in access. (I'm not sure if I've posted to the right place)

>you've asked this question several times but have never really advised us what the true background is ....
I can't think of a better description of my problem. My main problem is that I don't know so much about designing SQL statements and this

"SELECT CN1 AND CN3 FROM [MyTable] WHERE FINDROW = 'RN3'"

is just guessing from my side. I need help to validate if that works.

Maybe I must do somehting more with CN1 in the SELECT statement if my the table represents a spreadsheet?

>if so why not have your table normalised as

>x  , y,  value, formulae?

This looks interesting. Where can I read more about it? But if I use an Access database then it might be impossible.

This statemt works for me now.

"SELECT MyTable.MyColumn FROM MyTable WHERE (((MyTable.MyColumn)='Type1'))"

But this is just an example how I must access my table. So I'm we are not finished yet :)

Going to look at this now:

>select case @CN when "CN1" then CN1
>                  when "CN2" then CN2 ....
>                      end
>   from yourtable
>  where CN1 = @rownumber

I'm also using c# so I can build my sql string manualy. So, I need a stright example with SQL code that selects let's say the value 2 in this example table based on the column CN3 and row RN3.

CN1 CN2 CN3
RN1   7     5
RN2   5     3
RN3   9     2


At last I think that's what I need.

The row with the RN values is a regular cells with column name CN1

Thanks. I agree.

going by what u said



CN1 CN2 CN3
RN1   7     5
RN2   5     3
RN3   9     2

CN means column name and RN row name

"SELECT CN1 AND CN3 FROM [MyTable] WHERE FINDROW = 'RN3'"

Should give me the value 2



if u want CN2 based on CN1 value, then the last post by LowFatSpread will work for you

How low fat are you anyway, 2%?

ok, let me confirm something

are u going by rownumber? or a value in CN1?


If going by rownumber, have u thought of adding a Autonumber field
Im assuming u imported a spreadsheet and not linked it

If u add a autonumber, u will generate a number from 1 to ....

u can then use that as a rownumber

If linked then perhaps u can add a new column in that spreadsheet and add incremental values?


do you want to get a column number, i.e. u dont want to specify a column name, but by position

>are u going by rownumber? or a value in CN1?
a value in CN1

I actually have an index witch increase by one for each row. Just thought it would be easier the way I have designed my system when using characters in the CN1. Maybe I'm going to think about using the index instead.

>Im assuming u imported a spreadsheet and not linked it
It is linked

>do you want to get a column number, i.e. u dont want to specify a column name, but by position
I want to "zoom into" one cell and then select it. It's not so important if it is by an index number or character/string as long as it works

an index would probably be the best when I think about it..... it would cause problems if two rows in CN1 has the same value right?

Like this

CN1 CN2 CN3 CN4
  1   RN1   7     5
  2   RN2   5     3
  3   RN3   9     2

"SELECT CN1 AND CN4 FROM [MyTable] WHERE FINDROW = 3"

should give me the value 2

this looks odd. When using numbers, does not this search through CN1 and CN4 for the value 3? Then it would find two cells with value 3. right?

But when using RN1, RN2, RN3 as the index system it would work....

...I hope

ok, Im confused, I did it where u specified x and y

is it the other way round

u specify a value then find x and y?

If this is not possible with only using SELECT statement I must consider other solutions.

read the comments


Public Sub FindRowCol()

    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim j As Integer
    Dim iLookFor As Integer

'Find the value u want    
    iLookFor = 11

'Open the table containing your data
    Set rs = CurrentDb.OpenRecordset("Table2")
    Do While Not rs.EOF

        'Assume rownumber held in CN1
        'We check columns CN2 to CN5
        For j = 2 To 5
            If rs.Fields("CN" & j).Value = iLookFor Then
                MsgBox "Found in " & rs!cn1 & " - " & j
            End If
        Next j
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Sub

we are on the track I think... finding it by x and y is what I am looking for. I must just think a little now. I've never created functions in Access. How to do that?

more precise: finding the value by entering x and y

Think I finally got it now. This is actually select what I need. The FINDROW was just a joke. Do you agree?

strsql = "SELECT [CN3] FROM [MyTable] WHERE [CN1] = 'RN3'";

This gives me value 2! :)

I have not tested your functions yet. But thanks for helping. Is it possible to get this funtion outside Access when using etc OLEDB?

Thanks. Is it possible to use the VBA outside Access?

Thank you both alot