Table lookup function in Access

  • Thread starter Thread starter Capronzz
  • Start date Start date
C

Capronzz

Table relationships require 'exact' matches. I need a table lookup that
creates a 'match' when the 'key' in the table to be updated corresponds to a
range value(s) in the lookup table. The 'Between' idea would work. The
traditional way is to sort both files accending and transfer data to the
inquiry file if its key is equal or lower than the lookup file / table. I
have a table defining several hundred group names and IDs. and transactions
with thousands of different values (think money)to be assigned to said
groups.
 
I'm not exactly sure what you're trying to do, but it sounds like you get
there by:

1. Creating a sorted recordset with a where clause that shows all the
records greater than a certain date/time (I like that better than using the
key).
2. Adding the data from the first recordset to a table
 
Table relationships require 'exact' matches. I need a table lookup that
creates a 'match' when the 'key' in the table to be updated corresponds to a
range value(s) in the lookup table. The 'Between' idea would work. The
traditional way is to sort both files accending and transfer data to the
inquiry file if its key is equal or lower than the lookup file / table. I
have a table defining several hundred group names and IDs. and transactions
with thousands of different values (think money)to be assigned to said
groups.
Click to expand...

If you build the query in the SQL window instead of in the query design grid
you can use a "non equi join". You don't describe your lookup table in detail
but one (somewhat denormalized) way to do it is to have the lookup table have
three fields, Low, High, and Lookupvalue:

SELECT inquirytable.*, lookuptable.Lookupvalue
FROM inquirytable INNER JOIN lookuptable
ON inquirytable.key >= lookuptable.Low AND inquerytable.key <=
lookuptable.High;
 
Back
Top