SELECT in a table like an x and y coordinate system

Hello. I try to select based on row and column, so only one cell is selected where column and row cross each other. Like an x and y coordinate system where x=columns and y=rows.

Here is an example:

[MyTable]

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

But my SELECT statement is not right. I need some help to design it.
m-jansenAsked:
Who is Participating?
 
LowfatspreadCommented:
try

"SELECT CN3 FROM MyTable WHERE CN1 = 'RN3'"

ps case isn't supported by (earlier?) version of access

the "equivalent" is IIF

   IIF( expression , do for true , do for false )
0
 
LowfatspreadCommented:
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..

0
 
m-jansenAuthor Commented:
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");
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
m-jansenAuthor Commented:
The table represents a spreadsheet made by a wizard in access. (I'm not sure if I've posted to the right place)
0
 
m-jansenAuthor Commented:
>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?
0
 
m-jansenAuthor Commented:
>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.
0
 
m-jansenAuthor Commented:
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
0
 
m-jansenAuthor Commented:
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.
0
 
m-jansenAuthor Commented:
The row with the RN values is a regular cells with column name CN1
0
 
m-jansenAuthor Commented:
Thanks. I agree.
0
 
rockiroadsCommented:
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%?


0
 
rockiroadsCommented:
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

0
 
m-jansenAuthor Commented:
>are u going by rownumber? or a value in CN1?
a value in CN1
0
 
m-jansenAuthor Commented:
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.
0
 
m-jansenAuthor Commented:
>Im assuming u imported a spreadsheet and not linked it
It is linked
0
 
m-jansenAuthor Commented:
>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
0
 
m-jansenAuthor Commented:
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?
0
 
rockiroadsCommented:
If u going by a value in CN3

is it not easier just to perform a search on CN3?
u could create a parameter query

create a new query in Access

SELECT * from mytable
where CN1 = "RN" & [Enter Number]

Then enter your number, say 1
it returns record for RN1

Now to get your column, I dont think u can do it in SQL, so try in VBA

so try this


Public Function GetCell()

    Dim x As Integer
    Dim y As Integer
    Dim rv As Integer
   
    x = InputBox("Enter X")
    y = InputBox("Enter Y")
   
    rv = Val(Nz(DLookup("CN" & y, "Table2", "CN1='RN" & x & "'"), -1))
    MsgBox rv
End Function

0
 
m-jansenAuthor Commented:
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
0
 
m-jansenAuthor Commented:
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?
0
 
m-jansenAuthor Commented:
But when using RN1, RN2, RN3 as the index system it would work....
0
 
m-jansenAuthor Commented:
...I hope
0
 
rockiroadsCommented:
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?

0
 
m-jansenAuthor Commented:
If this is not possible with only using SELECT statement I must consider other solutions.
0
 
rockiroadsCommented:
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


0
 
m-jansenAuthor Commented:
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?
0
 
m-jansenAuthor Commented:
more precise: finding the value by entering x and y
0
 
rockiroadsCommented:
from your last comment before my post, it sounded like u wanted x and y given a value
oh well
given x,y, find value


open a new module in access

paste this code in


Public Function GetCell()

    Dim x As Integer
    Dim y As Integer
    Dim rv As Integer
   
    x = nz(InputBox("Enter X"),0)
    y = nz(InputBox("Enter Y"),0)
   
    if y >1 and x>=1 then
        rv = Val(Nz(DLookup("CN" & y, "Table2", "CN1='RN" & x & "'"), -1))
        MsgBox rv
     else
         msgbox "Invalid input"
    end if
End Function



then run it
this assumes rownumber prefixed with RN and is in CN1
0
 
m-jansenAuthor Commented:
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! :)
0
 
m-jansenAuthor Commented:
I have not tested your functions yet. But thanks for helping. Is it possible to get this funtion outside Access when using etc OLEDB?
0
 
rockiroadsCommented:
Your query u just used

does this look familiar

"SELECT CN3 FROM MyTable WHERE CN1 = 'RN3'"

was posted earlier, posted by LowFatSpread and I confirmed it
But I added the VBA to give u the flexibility of your own numbers

which was this query

SELECT * from mytable
where CN1 = "RN" & [Enter Number]


And the VBA was written to add more flexibility
0
 
m-jansenAuthor Commented:
Thanks. Is it possible to use the VBA outside Access?
0
 
rockiroadsCommented:
u can convert it to using vbscript if using ASP
u can use it as VB if using VB
u can create a macro which in turn runs this code (change to Function though) and u can call from command line
0
 
m-jansenAuthor Commented:
Thank you both alot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.