It's also quite possible to establish a relationship that looks up values from a table of your conversion values when a number in the first table falls in a specific range. This is a fairly simple thing to set up--provided you have a good structure to your database tables.
In particular: "within a test there can be at least a hundred different scores that need to be converted"
Those different scores should all be stored in a table with one score per record and then linked via a test ID to your Tests table. That way, you have one relationship linking a "score" record to your conversion table.
I'm a little unclear exactly what you mean. If I have the following mock tables set up how would another table be used?
(Each test is set up as its own table for the results. Each test has a relationship only back to a table with client info, related by Client ID. Tests A & B have results showing as Standard Scores, but Test C has results showing as T-Scores. For this example, let's say I need to retrieve the Percentile for each test. As I said, pulling the info back into the table would be ideal, but if I could only have a way to have the Percentile calculated and displayed on a report that would be fine)
Test A Test B Test C
Client ID 001 001 001
Result 1 110 91 34
Result 2 77 93 81
Result 3 141 72 52
Conversion Chart (Only using some of the 7+ columns provided, and some of the records of the actual 110+)
Standard Score T-Score Percentile
72 31 3
77 34 6
91 44 27
93 45 32
103 52 58
110 57 75
141 77 99.3
146 81 99.9
I appreciate the suggestion, and may end up needing to go with it, but i have fears of screwing up the math and want to see if there is a relational solution first before going down that path.
Your post illustrates what I meant about setting up the appropriate table structure so that the relationships are simple and easy to work with. Your data should be structured as:
Client ID TestID
Then you can set up a relationship from TestResults to the lookup table and you have only one such relationship instead of having to set up a separate look up relationship for each test.
It's still possible to display the data in the grid like your original example, BTW, if you use one form or another of a horizontal portal technique to organize the related data into columns if that is needed. (Horizontal portals are a series of one row portals placed side by side. They either list data from row 1, for the first portal, row 2 for the second, etc. or they use portal filter expressions to display the correct data in each column.)
Just so I have some clarity on your explanation (and that is hopefully inspite of my limited knowledge in this area), each answer to each test should be its own record(?). So if we have ten tests and they each have fifty questions, each new client will create 500 additional records in this table? That is not a problem, I just want to make sure I have a good understanding of what you are explaining.
Yes, that's the concept. Note that this approach then allows a flexible method for dealing with your answers. A test can have 3, 30 or 3,000 questions and the same setup continues to work, at least as far as the relationships, look ups and any calculations are concerned.
Great, thanks for the help. I will set up a few mock ups and see what I can do. I appreciate the insights.
Mike, You should also include the test type (Standard, T, etc.) with each test result record since the means and deviations of each test type are different.
I am trying to test this method out, but am running into some stumbling blocks. I have a form/layout setup to enter the various results. What field can the data be entered into? If I use 'Result,' it duplicates the entry across all records. I am sure there is a way to differentiate based on the Test ID, but I'm not sure how to set this up. I am also unclear as to which table(s) should be allowed to create new records based on the entry of new data.
We'd need to know more about what exactly you've set up here and the relationship options.
"allow creation of records..." in the relationship options only comes into play when you either using a portal to add new related records or when you directly modify a field in the table from a related table's layout to create the first (and only the first) such related record.
Entering a value in "result" is not duplicated the entry across all records, but it may look like that depending on how you've set this up.
While I feel that I get your suggestion on a basic level, I clearly have a problem understanding how to execute the concept. In trying to simplify so that I can get the concept down, I believe I would need four tables: ClientInfo, TestsTaken, TestResults, and the ConversionChart. Again for simplicity, let's say the tables consist of the following fields (lined up with the same named related fields next to each other):
ClientInfo TestsTaken TestResults ConversionChart
First Name TestID TestID
Last Name Result Result
My problem comes in how to create the records in the various tables each time someone goes in to enter results for a new client. I need an easy to read form for someone to go into, enter the client's info then enter the results across various tests. In dealing with the layouts my instinct is to drop in a field where a test result can be entered, and thereby creating a new TestResult record, but without the direct relation between TestResults and ClientInfo I can't figure out how to do this. Once that step is understood I do know how to pull the corresponding information from the ConversionChart.
Do you understand how to set up portals?
A portal to testsTaken can be placed on a ClientInfo layout to list the tests that they have taken and be used to log new tests taken. A portal to test results on a TestsTaken layout can show the results for that test and be used to record new results. That's the simplest to set up, though perhaps not the easiest to use. You'd find a client record on the client info layout, add new tests taken in that portal and click a button in the portal row to switch to the TestTaken layout in order to log the test results.
A more sophisticated approach puts portals to testResutls and TestsTaken on the ClientInfo layout. Selecting a row in the testsTaken portal triggers a script to display the test results for that test in the testResults portal. That approach takes additional relationships and scripting but makes it all happen on one layout.