calculation on a form

[jarheadjim]

I'm trying to figue out a way to make my database catagorize based on 2 variables, here's what i've got.

one variable is age, [age] and is entered directly into the form
the other is score, [pft total] and is a calculation performed with afterupdate functions.

what i would like to do is based on 4 age ranges and 3 score ranges (for each age) place the proper catagory (1st class, 2nd class, 3rd class) on the form.

for example a 27-36 yr old with a score of 200-300 is 1st class, but a 17-26 year old with a score of 225-300 is 1st class.

hope that's not to muddled

Jim

 

[x0reset]

You need 4NF table structure for this one.

lkpAgeRanges
-------------
AgeRangeID
LowerAge
UpperAge

lkpClasses
-----------
ClassID
ClassDescription

lnkAgeClasses
---------------
AgeRangeID
ClassID
LowerScore
UpperScore

This enough or shall I elaborate?

 

[jarheadjim]

Completely over my head

please elaborate a little, I am still WAAAAYYYYYY new

 

[x0reset]

Instead of entering an age, you need to select an age range. (lkpAgeRanges)

Specify a ClassID for each class. (lkpClasses)

Relate your AgeRangeID to ClassID. (lnkAgeClasses)

Your tables should look something like:

lkpAgeRanges
-------------
AgeRangeID LowerAge UpperAge
---------------------------------------------
1 18 22
2 23 27
3 28 32
etc...

lkpClasses
-----------
ClassID ClassDescription
----------------------------------
1 First Class
2 Second Class
3 Third Class

lnkAgeClasses
--------------
AgeRangeID ClassID LowerScore UpperScore
-----------------------------------------------------------------
1 1 0 100
1 2 101 200
1 3 201 300
2 1 0 75
2 2 76 150
etc...

This table structure lets you specify the range of scores that qualifies a given age range in a specific class.

During the data entry process have your users select the age range, then find which score range the [pft total] calculation falls in.

Hint: In your query add criteria where AgeRangeID=[UserSelection], LowerScore < [pft total], and UpperScore > [pft total].

Hope that helps.

 

[jarheadjim]

I need them to actually input an age, the report that will follow shows actual age. I have been trying using a seperate tables and treat this like a cascading combo box (each table has scores 1 - 300 and the class value for each score is in the second column) so based on age (ie "17" or "18" or "19"... select the appropriate table and display the class for the previously calculated score on THAT table. Am i heading down a losing path or does this appear workable?

 

[jarheadjim]

ok, new approach... I have all the class and scores in 1 table (5 columns) how would i go about making it display a differant column based on the selection of an age from a combobox.

column1 = score
column2 = young age group class
column3 = middle age group class
column4 = old age group class
column5 = dinosaur age group class

 

[jarheadjim]

still no joy

I'm beating my head against my desk as we speak...How in the world do I make a textbox show a particular column of a table based on a combobox's entry?