Uncommon Lookup

[teecad]

Hi All,

I have created a solution which includes new tables to store records and along with tables of records imported from Access databases. At least one of the imported databases is not formatted in such a way that I am easily able to perform lookups based upon related match fields. I have included a sample file to illustrate my predicament. Layout "Range1" illustrates the imported database while "Range2" illustrates what I consider to be a more conventional layout. The problem is I have approximately 30,000 records in the imported database that would have to be copied several times and split up to contain one term and rate per record if I am unable to perform lookups in its present arrangement. I am requesting assistance with the possibility of creating a look up of the range of field names in the "Range1" layout just as I would match the "term" field in "Range2" in a conventional lookup table. Any assistance would be much appreciated. Thanks in advance!

Test1.zip

[comment]

Why don't you import the data 5 times - each time importing the key and one of the rate columns.

[teecad]

Thank you for the reply. I could certainly do that, but I was wondering if there was a way to create the lookup based on the "Range1" database the way it is. Also, I submitted a sample file as an example. The actual file contains columns from 100°F to 1800°F, increasing by 50°F intervals. To reproduce the table in conventional form, I would be importing, probably with loop script, approx. 30,000 records 14 times each. It definitely is an option, and it may just be what I'll eventually have to do, but I was just exploring the option of using what I already have and it would be another learned work-around in the process.

[comment]

It's possible - though hardly advisable: you can use a Case() calculation with 14 tests.

BTW, please update your profile as you are clearly NOT using version 6.

[teecad]

Thank you for your comment, I guess I'll have to buckle down and update the table with repetitions of imports. Also, I apologize for the condition of my profile, thank you for calling it to my attention.