Conditional Lookup Table

[NavinBane]

I am writing a database in MS Access that will track and score customer information based on tests that they have taken. These tests are normalized by age category such that if you are age 20 and have a raw score=30 then standard score=69. I would like to have a form field for age which will accept user input for age i.e. "20" and then accept a raw score i.e. 30. The form would then auto-populate the standard score field by looking at the appropriate table for that age category (20) and the then linking the record for the standard score based on the raw score value.
I currently have tables that look like this
CCT 5 (tablename)
Raw Score Standard Score
30 69
31 72
32 75
etc...
I can't seem to get the raw score field to use a conditional to look up the appropriate table and values. Any help would be appreciated. I am very new to database design at all.
-NB

 

[lupins46]

You can use Dlookup

=DLookup("standardScore","CCT5","[Raw Score]= " & forms!formname!txtRawscore)

txtrawscore should be the name of your textbox containing the raw score field

 

[NavinBane]

Thank you. I will try that. I appreciate the quick response.

 

[NavinBane]

Thank you for the advice lupins46, but I'm not sure if that will work. Maybe I have it set up wrong, but at the moment I have the norms tables set up based on Age. So there is a table CCT5, a table CCT6, CCT7, etc.. I need it to use the input from the age field to tell the program which table to look in. The suggestion that gave appears to only look in the single table CCT5.

 

[PHV]

First of all, have a look here:

http://r937.com/relational.html

If after that you still insist on having many tables storing same sort of info you may consider the IIf function or the Select Case instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NavinBane]

I am willing to change the nature of how I store the data, but I am uncertain how to proceed. Here are the metrics that I have to store for lookup:
Standard Score (calculated from a Raw score based on Age defined normalized data) i.e. A raw score for a 12 year old would not equal the same standardized score as would the exact same raw score from a 15 year old.
Raw Score (manually entered)
Age (determines how the raw scores are normalized. I.e. raw score=15 is equal to standard score 58 for a 12 year old, but equal to standard score=89 for a 15 year old.

This is why I have had to set up the tables as seperate by age category. If there is a way that I can support all of this information in fewer tables, I would love to do it. I would appreciate any suggestions.

 

[PHV]

Simply have a age field (or two age fields for range) in only one CCT table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SeeThru]

Big table with all ages from 1 to 120 with conversion score for each would be simplest.

If you are feeling smart you could calculate it in a function using select case. Probably faster than Dlookup, too.

PS: Dont store the value in the table. Calculate it on the fly in reports and forms and queries. It means if you change how it is calculated, or change an input value you dont need to go back over everything.