Solved

SELECT in a table like an x and y coordinate system

Posted on 2006-06-28
35
330 Views
Last Modified: 2008-02-01
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
Comment
Question by:m-jansen
  • 24
  • 8
  • 2
35 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16999739
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
 

Author Comment

by:m-jansen
ID: 16999949
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
 

Author Comment

by:m-jansen
ID: 16999953
The table represents a spreadsheet made by a wizard in access. (I'm not sure if I've posted to the right place)
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:m-jansen
ID: 16999992
>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
 

Author Comment

by:m-jansen
ID: 17000005
>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
 

Author Comment

by:m-jansen
ID: 17000255
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
 

Author Comment

by:m-jansen
ID: 17000331
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
 

Author Comment

by:m-jansen
ID: 17000339
The row with the RN values is a regular cells with column name CN1
0
 

Author Comment

by:m-jansen
ID: 17000411
Thanks. I agree.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
ID: 17000419
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17000450
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17000477
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
 

Author Comment

by:m-jansen
ID: 17000526
>are u going by rownumber? or a value in CN1?
a value in CN1
0
 

Author Comment

by:m-jansen
ID: 17000565
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
 

Author Comment

by:m-jansen
ID: 17000584
>Im assuming u imported a spreadsheet and not linked it
It is linked
0
 

Author Comment

by:m-jansen
ID: 17000608
>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
 

Author Comment

by:m-jansen
ID: 17000629
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 250 total points
ID: 17000696
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
 

Author Comment

by:m-jansen
ID: 17000709
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
 

Author Comment

by:m-jansen
ID: 17000777
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
 

Author Comment

by:m-jansen
ID: 17000807
But when using RN1, RN2, RN3 as the index system it would work....
0
 

Author Comment

by:m-jansen
ID: 17000846
...I hope
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17001056
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
 

Author Comment

by:m-jansen
ID: 17001069
If this is not possible with only using SELECT statement I must consider other solutions.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17001177
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
 

Author Comment

by:m-jansen
ID: 17001216
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
 

Author Comment

by:m-jansen
ID: 17001251
more precise: finding the value by entering x and y
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 250 total points
ID: 17001304
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
 

Author Comment

by:m-jansen
ID: 17001381
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
 

Author Comment

by:m-jansen
ID: 17001465
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 250 total points
ID: 17001571
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
 

Author Comment

by:m-jansen
ID: 17003743
Thanks. Is it possible to use the VBA outside Access?
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 250 total points
ID: 17005240
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
 

Author Comment

by:m-jansen
ID: 17007207
Thank you both alot
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question