Data Lookup

lsutigernla

I am toying with different methods to look up data in a table. The table is one of a MySQL database and is structured like:

lookup_key int(14),
molow1 numeric(5,2),
mohigh1 numeric(5,2),
movalue1 numeric(5,2),
molow2 numeric(5,2),
mohigh2 numeric(5,2),
movalue2 numeric(5,2),
....
molow30 numeric(5,2),
mohigh30 numeric(5,2),
movalue30 numeric(5,2)

There are also other range prefixes with values such as fmlow1, dmlow1, etc. If a value falls between lowX and highX, I want to use valueX to perform a calculation.

The user will enter information to be looked up into a form ... basically the 'manuf' (lookup_key), 'molevel', 'fmlevel', 'dmlevel', etc. The first solution that comes to mind is to load the table row for that 'manuf' into an array and then walk all the 'mo's , 'fm's, etc. until I find the range where the user's 'molevel' is between 'molowX' and 'mohighX'. Then I know to use 'movalueX' to perform the required calculation.

If I have made myself clear enough, I would appreciate any input those on the board may have to offer. Is there a quicker or easier way?

Thanks In Advance!

wfa

tsinka

Hi,

which kind of data is stored in the table ? Things might become easier if you change the database layout a little bit. But what to do depends on the data stored in the table.

lsutigernla

Generally speaking, the table structure is what was laid out in the initial post. The table holds quality data for various manufacturers. There are about 10 different quality measures that all manufacturers will have a table of deductions. That is, they will all have a low and high range and a corresponding discount value for each sample falling within that high and low range.

The quality data is laid out like ...

Manufacturer "2341" values for 'mo' prefixed columns

Low High Discount
0 15.0 0.0
15.1 15.5 1.5
15.6 16.0 2.0
...

I'm sure that won't align properly but that is just the jest of it. All discount values may not follow a steady increase as shown in the example.

Did I answer what you were asking?

Thanks ... wfa :bemused:

tsinka

I think with the given table layout the only way is the one you suggested: Walk through the fields in the recordset until you find the matching one. A solution using more than one table would make things easier.

Thomas