• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

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.
0
m-jansen
Asked:
m-jansen
  • 24
  • 8
  • 2
5 Solutions
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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
 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 24
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now