Hi All,

Question: What is the most efficient way for me to use a pre-existing data set such as the one attached to this message? Basically, I already have a solution that contains fields for a person's name, age, and their time on a particular psychological test. I also have large excel tables that look like the one attached. My goal is to calculate my client's scaled scores by looking up where their age and time intersect on these tables.

These existing excel data tables is data that is not expected to change (I'm just using pre-existing data from large research studies to my score conversions). I'm wondering if it would be simpler to write large custom functions, or else how would I structure this data in my solution if I were to import these excel files over to my existing solution and once I have that data in my solution, how do I pull the result (e.g., Lookup?)

Edited October 22, 200817 yr by Guest
attached a better image of the file

You can have a reference table that store the same data in your Excel file. Each record would consist of a range, time, and score field. So basically for each intersection of data in Excel it should be its own record.

Then you can easily create a relationship to this new reference table to your Test table.

Another Suggestion, similar to above:

You could import these spreadsheets into a new table in your solution, but first you would need to reformat them. Something like the attached. Separate the low age and the high age into separate fields. Easy to do in Excel.

Then you would have a relationship from your existing table something like this

Client:Age >= Data:Low_age

Client:Age <= Data:High_age

Client:time = Seconds

This will give you the related number in your Data Table.

See attached example

Reformatted.zip

That sounds simple the way you put it. Thanks!

Thanks again, that helps as well.