Question Multi-criteria lookup from table

Midavalo

Midavalo

New member
Joined
May 26, 2015
Messages
3
Location
New Zealand
Programming Experience
Beginner
Hi,

This is my first question here, hopefully I've put it in the correct place.

I'm trying to build a small app (for fun, at home) that looks up values based on two input values. I had thought I'd create a simple MS Access table and look up from that, but I just can't figure out how to connect to the Access table and return any values. I'm using Visual Studio 2013 and Access 2013.

Firstly, is an Access table the best place to store and lookup these values? I have previously pulled values from a my.settings file (in a different project), but there are no lookups so that was fairly easy to setup.

Secondly how do I perform the lookup and get the value returned?
There are 57 rows and 7 columns. First value I want to query is from the first column, second value determines which of the remaining 6 columns to select from (in the form of selection from 1-6), so using the table below, if I input 20000 in the first textbox and 4 in the second textbox it would return a value of 103.

[TABLE="width: 500"]
[TR]
[TD="align: center"]Amount
[/TD]
[TD="align: center"]V1
[/TD]
[TD="align: center"]V2
[/TD]
[TD="align: center"]V3
[/TD]
[TD="align: center"]V4
[/TD]
[TD="align: center"]V5
[/TD]
[TD="align: center"]V6
[/TD]
[/TR]
[TR]
[TD="align: right"]10000[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: right"]20000[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD="align: right"]30000[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]135[/TD]
[/TR]
[TR]
[TD="align: right"]40000[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1200[/TD]
[/TR]
[/TABLE]


Alternatively I could pivot it slightly, so would lookup 20000 from first column and 4 from second column would return 103 from third column.
[TABLE="width: 300"]
[TR]
[TD="align: center"]Amount
[/TD]
[TD="align: center"]Count
[/TD]
[TD="align: center"]Value
[/TD]
[/TR]
[TR]
[TD="align: right"]10000
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD="align: right"]10000
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]20
[/TD]
[/TR]
[TR]
[TD="align: right"]10000
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD="align: right"]10000[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]40
[/TD]
[/TR]
[TR]
[TD="align: right"]10000
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]50
[/TD]
[/TR]
[TR]
[TD="align: right"]10000
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]60
[/TD]
[/TR]
[TR]
[TD="align: right"]20000
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]100
[/TD]
[/TR]
[TR]
[TD="align: right"]20000
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]101
[/TD]
[/TR]
[TR]
[TD="align: right"]20000[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]102
[/TD]
[/TR]
[TR]
[TD="align: right"]20000
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]103
[/TD]
[/TR]
[TR]
[TD="align: right"]20000[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]104
[/TD]
[/TR]
[TR]
[TD="align: right"]20000
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]105
[/TD]
[/TR]
[TR]
[TD="align: right"]30000
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]110
[/TD]
[/TR]
[TR]
[TD="align: right"]30000
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]115
[/TD]
[/TR]
[TR]
[TD="align: right"]30000[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]120
[/TD]
[/TR]
[TR]
[TD="align: right"]30000
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]125
[/TD]
[/TR]
[TR]
[TD="align: right"]30000
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]130
[/TD]
[/TR]
[TR]
[TD="align: right"]30000
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]135
[/TD]
[/TR]
[TR]
[TD="align: right"]40000
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]200
[/TD]
[/TR]
[TR]
[TD="align: right"]40000[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]400
[/TD]
[/TR]
[TR]
[TD="align: right"]40000
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]600
[/TD]
[/TR]
[TR]
[TD="align: right"]40000[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]800
[/TD]
[/TR]
[TR]
[TD="align: right"]40000
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]1000
[/TD]
[/TR]
[TR]
[TD="align: right"]40000
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]1200
[/TD]
[/TR]
[/TABLE]

Please let me know if there is any other info I need to supply.

Cheers,
Mike.
 
Sort by date Sort by votes
For a small amount of data, you could use any number of options but a database is fine and good practice for more complex projects. You would use ADO.NET to connect to and manipulate the database. Create an OleDbCommand containing the appropriate SQL code and then execute it with a data reader or data adapter. There's plenty of information around about ADO.NET in general and with Access in particular. Post back if you have specific issues.
 
Upvote 0 Downvote
jmcilhinney said:
For a small amount of data, you could use any number of options but a database is fine and good practice for more complex projects. You would use ADO.NET to connect to and manipulate the database. Create an OleDbCommand containing the appropriate SQL code and then execute it with a data reader or data adapter. There's plenty of information around about ADO.NET in general and with Access in particular. Post back if you have specific issues.
Click to expand...
Thanks jmcilhinney, with something extra to search on (ADO.Net) I was able to find some helpful pointers at https://msdn.microsoft.com/en-us/library/vstudio/dw70f090(v=vs.110).aspx which I was able to modify to work with my table and query :) Much appreciated.

Here's the code I copied from that site (pre-modification)
VB.NET:
Expand Collapse Copy 
[COLOR=Blue]Option[/COLOR] [COLOR=Blue]Explicit[/COLOR] [COLOR=Blue]On[/COLOR] 
[COLOR=Blue]Option[/COLOR] Strict [COLOR=Blue]On[/COLOR] 

[COLOR=Blue]Imports[/COLOR] System
[COLOR=Blue]Imports[/COLOR] System.Data
[COLOR=Blue]Imports[/COLOR] System.Data.OleDb

[COLOR=Blue]Public[/COLOR] [COLOR=Blue]Class[/COLOR] Program
    [COLOR=Blue]Public[/COLOR] [COLOR=Blue]Shared[/COLOR] [COLOR=Blue]Sub[/COLOR] Main()

        [COLOR=Green]' The connection string assumes that the Access  [/COLOR]
        [COLOR=Green]' Northwind.mdb is located in the c:\Data folder. [/COLOR]
        [COLOR=Blue]Dim[/COLOR] connectionString [COLOR=Blue]As[/COLOR] [COLOR=Blue]String[/COLOR] = _
             [COLOR=#A31515]"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="[/COLOR] _
            & [COLOR=#A31515]"c:\Data\Northwind.mdb;User Id=admin;Password=;"[/COLOR] 

        [COLOR=Green]' Provide the query string with a parameter placeholder. [/COLOR]
        [COLOR=Blue]Dim[/COLOR] queryString [COLOR=Blue]As[/COLOR] [COLOR=Blue]String[/COLOR] = _
            [COLOR=#A31515]"SELECT ProductID, UnitPrice, ProductName from Products "[/COLOR] _
            & [COLOR=#A31515]"WHERE UnitPrice > ? "[/COLOR] _
            & [COLOR=#A31515]"ORDER BY UnitPrice DESC;"[/COLOR] 

        [COLOR=Green]' Specify the parameter value. [/COLOR]
        [COLOR=Blue]Dim[/COLOR] paramValue [COLOR=Blue]As[/COLOR] [COLOR=Blue]Integer[/COLOR] = 5

        [COLOR=Green]' Create and open the connection in a using block. This [/COLOR]
        [COLOR=Green]' ensures that all resources will be closed and disposed [/COLOR]
        [COLOR=Green]' when the code exits. [/COLOR]
        [COLOR=Blue]Using[/COLOR] connection [COLOR=Blue]As[/COLOR] [COLOR=Blue]New[/COLOR] OleDbConnection(connectionString)

            [COLOR=Green]' Create the Command and Parameter objects. [/COLOR]
            [COLOR=Blue]Dim[/COLOR] command [COLOR=Blue]As[/COLOR] [COLOR=Blue]New[/COLOR] OleDbCommand(queryString, connection)
            command.Parameters.AddWithValue([COLOR=#A31515]"@pricePoint"[/COLOR], paramValue)

            [COLOR=Green]' Open the connection in a try/catch block.  [/COLOR]
            [COLOR=Green]' Create and execute the DataReader, writing the result [/COLOR]
            [COLOR=Green]' set to the console window. [/COLOR]
            [COLOR=Blue]Try[/COLOR]
                connection.Open()
                [COLOR=Blue]Dim[/COLOR] dataReader [COLOR=Blue]As[/COLOR] OleDbDataReader = _
                 command.ExecuteReader()
                [COLOR=Blue]Do[/COLOR] [COLOR=Blue]While[/COLOR] dataReader.Read()
                    Console.WriteLine( _
                        vbTab & [COLOR=#A31515]"{0}"[/COLOR] & vbTab & [COLOR=#A31515]"{1}"[/COLOR] & vbTab & [COLOR=#A31515]"{2}"[/COLOR], _
                     dataReader(0), dataReader(1), dataReader(2))
                [COLOR=Blue]Loop[/COLOR]
                dataReader.Close()

            [COLOR=Blue]Catch[/COLOR] ex [COLOR=Blue]As[/COLOR] Exception
                Console.WriteLine(ex.Message)
            [COLOR=Blue]End[/COLOR] [COLOR=Blue]Try[/COLOR]
            Console.ReadLine()
        [COLOR=Blue]End[/COLOR] [COLOR=Blue]Using[/COLOR] 
    [COLOR=Blue]End[/COLOR] [COLOR=Blue]Sub[/COLOR] 
[COLOR=Blue]End[/COLOR] [COLOR=Blue]Class[/COLOR]

Thanks again,
Mike.
 
Upvote 0 Downvote

Similar threads

chacham
  • Question Question
Databind combobox to child table, show values from parent table
Replies
1
Views
2K
chacham
chacham
S
  • Solved
Question how to retrieve row, update and lock from another user?
Replies
1
Views
3K
servox
S
T
  • Question Question
scrape html table - question...
Replies
0
Views
3K
TeachMe
T
C
  • Question Question
Question External Application Lcoation and Multi-Displays
Replies
1
Views
3K
JohnH
JohnH
E
  • Question Question
Question Recursive CTE Solution
Replies
3
Views
4K
elroyskimms
E

Share this page

Latest posts

Back
Top