Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SELECT in a table like an x and y coordinate system

Posted on 2006-06-28
35
Medium Priority
?
341 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
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!

 

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

Independent Software Vendors: 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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

886 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