compound primary key using calculated field

[quaddawg via AccessMonster.com]

I've made a lookup table with a primary key based on age and total score (if
the test taker is a certain age and scores a certain total score, various
other fields such as percentile rank are "looked-up"). However, the total
score can change based on changes to sub-tests. Accordingly, I'd like the
total score to be calculated so that edits to sub-tests won't require me to
recalculate and re-enter the total score. Will I be able to reconcile these
aims with this set-up somehow?
If not, is there a better way to make a matrix to lookup related info based
on age and total score?

 

[Rod Plastow]

Yes it's possible to derive a total score from composite sub scores.

But ...

One guideline of data normalisation is not to store derived values but to
recalculate them each time you need them. Total score is a derived value.
The reason for this guideline is exactly your situation!

I think you have made a bad choice of primary key. A guideline for database
design is not to choose values that you know will change as primary keys or
components thereof. Furthermore primary keys must be unique; I hope in your
case you can't have two different scores for someone aged 62.

I suspect you are actually keeping the maximum or average score rather than
individual scores. This is a situation where you may well relax the derived
value guideline and keep the calculated value on your database.

OK, if you want to persist with your present design let me know how the sub
scores get changed (i.e. through a form or directly on the table(s)) and the
arithmetic that calculates the total score and I will try to take this a step
further.

Rod

 

[quaddawg via AccessMonster.com]

Thanks for your response. Let me backup and explain what I'm trying to
accomplish:

I'm trying this lookup table in order to automate scoring. As it stands, I
have printed spreadsheets, with each sheet representing an age range of three
months (e.g. 8 years 2 months - 8 years 4 months), with total scores
descending down the Y axis. Across the X axis are fields such as standard
score, percentile rank, etc. So, if the student is 8 years 2 months old and
scored 138, he has a given standard score, etc. Rather than fill in all of
this information repeatedly (whether in excel or access) for each student, I
wanted to automate the process so that, when the age and total score are
known and represented in the Tests table, it will pull up the extra fields
from the lookup table corresponding to the age and total score. I was trying
to accomplish this by using the compound primary key (autonumber would not
make sense for a lookup table): age and total score equals x and y in Tests
table, it links to lookup table with same age and total score (so, yes,
they're unique-only one entry in the lookup table for 8:2/138).

The problem is, the age and total score fields should be calculated fields,
so that corrections to birthdates, testdates, or subtests in the Tests table
(through a form of course) will automatically yield correct ages and total
scores.

Another idea, can I have calculated age and total score controls on a form
serve as the linking lookup "primary key" values of the lookup table? If so,
how would I capture this for reporting?

I think I'm missing something obvious here.

 

[Rod Plastow]

Hi,

Ok, I understand better but not fully.

You have/want a table that contains standard score, percentile rank, etc.
for any given combination of age range and actual total score? I assume that
standard score, percentile rank, etc. are constant; that they do not change
according to the population of actual scores? If they do change, that is if
they are a function of the actual total score population then it's back to
the drawing board.

Look-up tables as you call them are nothing untoward. The first point I
want to make is that you do not have to make the look-up values the primary
key. You could if you wish have an arbitrary primary key such as an
autonumber. What you should do however is to declare your look-up keys to be
included in one or more indices. You might define separate indices for age
range and total score or you might define one compound index such as you
describe in your post: age range concatenated with total score. The only
draw back with the latter is that it is easy to search on age range and find
all the corresponding total scores but difficult to do the other way around,
that is search on total scores and find all age ranges. The benefit is that
you can specify that combinations must be unique. The decision rests with
how you will access your data.

Now I am a little confused. Your first post indicated that your key values
would change but from your second post it seems that age range and total
score are static.

Could it be that your difficulty lies with the look-up values themselves?
Yes change a birthdate for an individual and their age range may be
different; change an individual score and the total score changes.

OK, you need algorithms to (re)calculate age range and total score. These
could easily be implemented as VBA functions - either global (public)
functions if called from various places or local (private) functions defined
within the form where they are used.

To use these functions simply trap an appropriate event - Before Update is a
popular choice because the update can be cancelled - and call the function.
You probably want to display the returned result in a field on the form. Let
me try to make this a little more explicit.

Assume you have two fields on your form: BirthDate and AgeRange, the latter
being a calculated field. In the Before Update event for BirthDate code
something like

AgeRange = MyFunction(BirthDate)

For the total score you can simply specify the Control Source of the
TotalScore field to be

= [Score01]+[Score02]+[ ...

Now for the look-up. Why not use a series of DLookups. The syntax - well
the parentheses - can be a little alarming for text variables. I assume that
TotalScore is numeric but for this example let me assume AgeRange is text.
For each looked-up value you need some code such as

StandardScore = DLookup("StandardScore","LookupTable","TotalScore = " &
TotalScore & " AND AgeRange = """ & AgeRange & """)

Obviously substitute your own names in the above.

Where should you put this code? I suggest in the After Update events for
TotalScore and AgeRange - I think the events fire when you update the fields
programmatically.

I realise this post is somewhat involved and I have made many assumptions.
Please get back to me if you need further help. Please accept my apologies
if I've pitched the post at too elementary a level.

One last point: a series of DLookups is not as efficient as a single record
retrieval if you want to retrieve two or more values. However if you don't
know DAO or ADO then DLookup is a good place to start and get your
application working. Change it to a more efficient db access later.

Rod

 

[quaddawg via AccessMonster.com]

You've given me a lot to chew on. I'll report back.
Thanks

Hi,

Ok, I understand better but not fully.

You have/want a table that contains standard score, percentile rank, etc.
for any given combination of age range and actual total score? I assume that
standard score, percentile rank, etc. are constant; that they do not change
according to the population of actual scores? If they do change, that is if
they are a function of the actual total score population then it's back to
the drawing board.

Look-up tables as you call them are nothing untoward. The first point I
want to make is that you do not have to make the look-up values the primary
key. You could if you wish have an arbitrary primary key such as an
autonumber. What you should do however is to declare your look-up keys to be
included in one or more indices. You might define separate indices for age
range and total score or you might define one compound index such as you
describe in your post: age range concatenated with total score. The only
draw back with the latter is that it is easy to search on age range and find
all the corresponding total scores but difficult to do the other way around,
that is search on total scores and find all age ranges. The benefit is that
you can specify that combinations must be unique. The decision rests with
how you will access your data.

Now I am a little confused. Your first post indicated that your key values
would change but from your second post it seems that age range and total
score are static.

Could it be that your difficulty lies with the look-up values themselves?
Yes change a birthdate for an individual and their age range may be
different; change an individual score and the total score changes.

OK, you need algorithms to (re)calculate age range and total score. These
could easily be implemented as VBA functions - either global (public)
functions if called from various places or local (private) functions defined
within the form where they are used.

To use these functions simply trap an appropriate event - Before Update is a
popular choice because the update can be cancelled - and call the function.
You probably want to display the returned result in a field on the form. Let
me try to make this a little more explicit.

Assume you have two fields on your form: BirthDate and AgeRange, the latter
being a calculated field. In the Before Update event for BirthDate code
something like

AgeRange = MyFunction(BirthDate)

For the total score you can simply specify the Control Source of the
TotalScore field to be

= [Score01]+[Score02]+[ ...

Now for the look-up. Why not use a series of DLookups. The syntax - well
the parentheses - can be a little alarming for text variables. I assume that
TotalScore is numeric but for this example let me assume AgeRange is text.
For each looked-up value you need some code such as

StandardScore = DLookup("StandardScore","LookupTable","TotalScore = " &
TotalScore & " AND AgeRange = """ & AgeRange & """)

Obviously substitute your own names in the above.

Where should you put this code? I suggest in the After Update events for
TotalScore and AgeRange - I think the events fire when you update the fields
programmatically.

I realise this post is somewhat involved and I have made many assumptions.
Please get back to me if you need further help. Please accept my apologies
if I've pitched the post at too elementary a level.

One last point: a series of DLookups is not as efficient as a single record
retrieval if you want to retrieve two or more values. However if you don't
know DAO or ADO then DLookup is a good place to start and get your
application working. Change it to a more efficient db access later.

Rod

 

[Rod Plastow]

Don't be shy.

 

[quaddawg via AccessMonster.com]

In the meantime, let me just say that every field in the lookup table is, and
will remain, static; the changing or calculated fields occur in tblTests and
tblStudents (might need to edit a test date or subtest score in tblTests, or
a birth date in tblStudents). I just wanted an easy way to "connect" these
two tables with the lookup table. Right now, I am using a public function to
calculate age and summing the subtests in a query. I was hoping to get to
the point where I could use "ageattesting" and "totalscore," which are
calculated fields, to lookup the other five or so fields resulting from a
given age and score.
I will work through your suggestions and write back.
Thanks again,
Lawton

Hi,

Ok, I understand better but not fully.

You have/want a table that contains standard score, percentile rank, etc.
for any given combination of age range and actual total score? I assume that
standard score, percentile rank, etc. are constant; that they do not change
according to the population of actual scores? If they do change, that is if
they are a function of the actual total score population then it's back to
the drawing board.

Look-up tables as you call them are nothing untoward. The first point I
want to make is that you do not have to make the look-up values the primary
key. You could if you wish have an arbitrary primary key such as an
autonumber. What you should do however is to declare your look-up keys to be
included in one or more indices. You might define separate indices for age
range and total score or you might define one compound index such as you
describe in your post: age range concatenated with total score. The only
draw back with the latter is that it is easy to search on age range and find
all the corresponding total scores but difficult to do the other way around,
that is search on total scores and find all age ranges. The benefit is that
you can specify that combinations must be unique. The decision rests with
how you will access your data.

Now I am a little confused. Your first post indicated that your key values
would change but from your second post it seems that age range and total
score are static.

Could it be that your difficulty lies with the look-up values themselves?
Yes change a birthdate for an individual and their age range may be
different; change an individual score and the total score changes.

OK, you need algorithms to (re)calculate age range and total score. These
could easily be implemented as VBA functions - either global (public)
functions if called from various places or local (private) functions defined
within the form where they are used.

To use these functions simply trap an appropriate event - Before Update is a
popular choice because the update can be cancelled - and call the function.
You probably want to display the returned result in a field on the form. Let
me try to make this a little more explicit.

Assume you have two fields on your form: BirthDate and AgeRange, the latter
being a calculated field. In the Before Update event for BirthDate code
something like

AgeRange = MyFunction(BirthDate)

For the total score you can simply specify the Control Source of the
TotalScore field to be

= [Score01]+[Score02]+[ ...

Now for the look-up. Why not use a series of DLookups. The syntax - well
the parentheses - can be a little alarming for text variables. I assume that
TotalScore is numeric but for this example let me assume AgeRange is text.
For each looked-up value you need some code such as

StandardScore = DLookup("StandardScore","LookupTable","TotalScore = " &
TotalScore & " AND AgeRange = """ & AgeRange & """)

Obviously substitute your own names in the above.

Where should you put this code? I suggest in the After Update events for
TotalScore and AgeRange - I think the events fire when you update the fields
programmatically.

I realise this post is somewhat involved and I have made many assumptions.
Please get back to me if you need further help. Please accept my apologies
if I've pitched the post at too elementary a level.

One last point: a series of DLookups is not as efficient as a single record
retrieval if you want to retrieve two or more values. However if you don't
know DAO or ADO then DLookup is a good place to start and get your
application working. Change it to a more efficient db access later.

Rod

 

[Jamie Collins]

You've given me a lot to chew on. I'll report back.

I agree with the 'nothing untoward' assessment.

Here's a quick example (ANSI-92 Query Mode SQL DDL syntax):

CREATE TABLE Grades (
grade_description VARCHAR(12) NOT NULL,
age_limit_lower INTEGER NOT NULL,
age_limit_upper INTEGER NOT NULL,
grade_limit_lower DECIMAL(5, 2) NOT NULL,
grade_limit_upper DECIMAL(5, 2) NOT NULL
)
;
CREATE TABLE People (
person_name VARCHAR(35) NOT NULL,
age INTEGER NOT NULL
)
;
CREATE TABLE Scores (
person_name VARCHAR(35) NOT NULL,
score DECIMAL(5, 2) NOT NULL
)
;

INSERT INTO Grades (grade_description, age_limit_lower,
age_limit_upper, grade_limit_lower, grade_limit_upper) VALUES ('Pass',
6, 13, 0.00, 39.99)
;
INSERT INTO Grades (grade_description, age_limit_lower,
age_limit_upper, grade_limit_lower, grade_limit_upper) VALUES
('Distinction', 6, 13, 40.00, 100.00)
;
INSERT INTO Grades (grade_description, age_limit_lower,
age_limit_upper, grade_limit_lower, grade_limit_upper) VALUES ('Fail',
14, 999, 0.00, 39.99)
;
INSERT INTO Grades (grade_description, age_limit_lower,
age_limit_upper, grade_limit_lower, grade_limit_upper) VALUES ('Pass',
14, 999, 40.00, 64.99)
;
INSERT INTO Grades (grade_description, age_limit_lower,
age_limit_upper, grade_limit_lower, grade_limit_upper) VALUES ('Gold',
14, 999, 65.00, 74.99)
;
INSERT INTO Grades (grade_description, age_limit_lower,
age_limit_upper, grade_limit_lower, grade_limit_upper) VALUES
('Platinum', 14, 999, 75.00, 100.99)
;
INSERT INTO People (person_name, age) VALUES ('ChildOne', 8)
;
INSERT INTO People (person_name, age) VALUES ('ChildTwo', 13)
;
INSERT INTO People (person_name, age) VALUES ('AdultOne', 22)
;
INSERT INTO People (person_name, age) VALUES ('AdultTwo', 55)
;
INSERT INTO Scores (person_name, score) VALUES ('ChildOne', 23.45)
;
INSERT INTO Scores (person_name, score) VALUES ('ChildTwo', 67.89)
;
INSERT INTO Scores (person_name, score) VALUES ('AdultOne', 34.56)
;
INSERT INTO Scores (person_name, score) VALUES ('AdultTwo', 78.90)
;
SELECT P1.person_name, S1.score, G1.grade_description
FROM (Scores AS S1
INNER JOIN People AS P1
ON P1.person_name = S1.person_name), Grades AS G1
WHERE S1.score BETWEEN G1.grade_limit_lower AND G1.grade_limit_upper
AND P1.age BETWEEN G1.age_limit_lower AND G1.age_limit_upper
;

And here it is as VBA to create a new mdb, tables and data:

Sub LookupMultiCriteria()
' Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim sql As String
sql = _
"CREATE TABLE Grades (" & vbCr & "grade_description" & _
" VARCHAR(12) NOT NULL, " & vbCr & "age_limit_lower" & _
" INTEGER NOT NULL, " & vbCr & "age_limit_upper" & _
" INTEGER NOT NULL, " & vbCr & "grade_limit_lower" & _
" DECIMAL(5, 2) NOT NULL, " & vbCr & "grade_limit_upper" & _
" DECIMAL(5, 2) NOT NULL" & vbCr & ")"
..Execute sql
sql = _
"CREATE TABLE People (" & vbCr & "person_name" & _
" VARCHAR(35) NOT NULL, " & vbCr & "age INTEGER" & _
" NOT NULL" & vbCr & ")"
..Execute sql
sql = _
"CREATE TABLE Scores (" & vbCr & "person_name" & _
" VARCHAR(35) NOT NULL, " & vbCr & "score" & _
" DECIMAL(5, 2) NOT NULL" & vbCr & ")"
..Execute sql
sql = _
"INSERT INTO Grades (grade_description," & _
" age_limit_lower, age_limit_upper," & _
" grade_limit_lower, grade_limit_upper)" & _
" VALUES ('Pass', 6, 13, 0.00," & _
" 39.99)"
..Execute sql
sql = _
"INSERT INTO Grades (grade_description," & _
" age_limit_lower, age_limit_upper," & _
" grade_limit_lower, grade_limit_upper)" & _
" VALUES ('Distinction', 6, 13," & _
" 40.00, 100.00)"
..Execute sql
sql = _
"INSERT INTO Grades (grade_description," & _
" age_limit_lower, age_limit_upper," & _
" grade_limit_lower, grade_limit_upper)" & _
" VALUES ('Fail', 14, 999, 0.00," & _
" 39.99)"
..Execute sql
sql = _
"INSERT INTO Grades (grade_description," & _
" age_limit_lower, age_limit_upper," & _
" grade_limit_lower, grade_limit_upper)" & _
" VALUES ('Pass', 14, 999, 40.00," & _
" 64.99)"
..Execute sql
sql = _
"INSERT INTO Grades (grade_description," & _
" age_limit_lower, age_limit_upper," & _
" grade_limit_lower, grade_limit_upper)" & _
" VALUES ('Gold', 14, 999, 65.00," & _
" 74.99)"
..Execute sql
sql = _
"INSERT INTO Grades (grade_description," & _
" age_limit_lower, age_limit_upper," & _
" grade_limit_lower, grade_limit_upper)" & _
" VALUES ('Platinum', 14, 999," & _
" 75.00, 100.99)"
..Execute sql
sql = _
"INSERT INTO People (person_name," & _
" age) VALUES ('ChildOne', 8)"
..Execute sql
sql = _
"INSERT INTO People (person_name," & _
" age) VALUES ('ChildTwo', 13)"
..Execute sql
sql = _
"INSERT INTO People (person_name," & _
" age) VALUES ('AdultOne', 22)"
..Execute sql
sql = _
"INSERT INTO People (person_name," & _
" age) VALUES ('AdultTwo', 55)"
..Execute sql
sql = _
"INSERT INTO Scores (person_name," & _
" score) VALUES ('ChildOne', 23.45)"
..Execute sql
sql = _
"INSERT INTO Scores (person_name," & _
" score) VALUES ('ChildTwo', 67.89)"
..Execute sql
sql = _
"INSERT INTO Scores (person_name," & _
" score) VALUES ('AdultOne', 34.56)"
..Execute sql
sql = _
"INSERT INTO Scores (person_name," & _
" score) VALUES ('AdultTwo', 78.90)"
..Execute sql
sql = _
"CREATE VIEW PersonGrades AS" & _
" SELECT P1.person_name, S1.score," & _
" G1.grade_description " & vbCr & "FROM (Scores" & _
" AS S1 " & vbCr & "INNER JOIN People AS P1" & _
" " & vbCr & "ON P1.person_name = S1.person_name)," & _
" Grades AS G1 " & vbCr & "WHERE S1.score" & _
" BETWEEN G1.grade_limit_lower" & _
" AND G1.grade_limit_upper" & vbCr & "AND" & _
" P1.age BETWEEN G1.age_limit_lower" & _
" AND G1.age_limit_upper"
..Execute sql
sql = _
"SELECT V1.person_name, V1.score," & _
" V1.grade_description " & vbCr & "FROM PersonGrades" & _
" AS V1"
Dim rs
Set rs = .Execute(sql)
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[quaddawg via AccessMonster.com]

This is beyond my current abilities, but I'll see if I can make sense of it
nonetheless.
Thanks for your help.

 

[Michael Gramelspacher]

This is beyond my current abilities, but I'll see if I can make sense of it
nonetheless.
Thanks for your help.

Just a little unsolicited help. Create a new blank database.
Create a new module. Copy all Jamies's code between Sub LookupMultiCriteria and
End Sub, and past it into the new module. Save it. In the Immediate Window
type call LookupMultiCriteria and press enter. That's it.

On my computer the database DropMe.mdb was created in the folder
C:\Documents and Settings\Mike Gramelspacher\Local Settings\Temp