Solved

SELECT in a table like an x and y coordinate system

Posted on 2006-06-28
35
327 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now