Finding Maximum Score

[Tom Tripicchio]

I have a form that has 3 trial scores, I would like a field in the same form
to show me the maximum score of the 3 trials.

How can this be done?

Thanks, Tom

 

[Jeff Boyce]

Tom

That depends...

Are the three scores held in three separate fields in the underlying table?
If so, this describes ... a spreadsheet! Looking "across" fields for a
maximum is something that you'd do in Excel, but Access is a relational
database, and provides relationally-oriented features and functions.

In a well-normalized Access table, finding the maximum of a set of scores
involves looking "down" (across multiple records), not "across" (within the
same record).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tom Tripicchio]

Thanks for the response Jeff,

I did it that way because the 3 trials are all tied to the same date. An
example would be that we ask a patient to perform a task 3 times and use the
highest score. But I need to capture the score of all 3 tests. Then I would
graph the improvement over time of the maximum scores.

If there is another way I am open.

Tom

 

[Jeff Boyce]

Tom

There is another way. As one of my fellow MVPs (John V, I believe) is wont
to say, "skinny and deep, not shallow and wide".

If you create a table that stores test results vertically, not horizontally,
you could use Access' built in Maximum function in a query. Not so if you
use three fields in the same record. Here's a sample table (your mileage
may vary):

trelTestResults
TestResultID
PatientID (which patient)
TestID (which test)
TrialNumber (which trial)
TestResult (I don't know what's being tested, so this is a generic
placeholder)
TestDate

With a structure like this, you can use a query to find, for PatientID = 7
(or which ever one you want), for TestID = 17 (you know the drill), all test
result rows (sorted by TestDate, you have a history of when the individual
trials were taken). Then create a second query, based on the first, using
the Totals query, and Max on the TestDate field to find when the most recent
test was taken.

Or have I misunderstood?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Phil Smith]

You have try1, try2, and try3. Those are the fields with each score.

Use an Iif to give you the greater of try1 and try2, then use an iif to
give you the greater of that, or try3.


Iif (
iif([try1]>[try2],[try1],[try2])>[try3],
iif([try1]>[try2],[try1],[try2]),
[try3])

Phil

 

[Jeff Boyce]

Phil

While this expression may work (may, because Nulls could mess with it) when
there are exactly three tries (as the OP stated), there will need to be a
major maintenance effort made if the number of tries ever changes. The
table will change, any queries referring to those fields will change, any
forms using those fields will change, any code/macros ... (you get the
picture).

In the long run, the OP will either pay now (normalizing the data) or pay
later (increased maintenance). Or, there's always Excel?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

You have try1, try2, and try3. Those are the fields with each score.

Use an Iif to give you the greater of try1 and try2, then use an iif to
give you the greater of that, or try3.


Iif (
iif([try1]>[try2],[try1],[try2])>[try3],
iif([try1]>[try2],[try1],[try2]),
[try3])

Phil

Thanks for the response Jeff,

I did it that way because the 3 trials are all tied to the same date. An
example would be that we ask a patient to perform a task 3 times and use
the highest score. But I need to capture the score of all 3 tests. Then I
would graph the improvement over time of the maximum scores.

If there is another way I am open.

Tom

 

[Phil Smith]

Certianly, but he can get his stuff working quickly, and have a little
breathing room to make the more labor intensive changes to normalize his
database. To fix the nulls, he could wrap all of the [tryx] in a NZ().

Not suggesting my way is best, or yours is bad, just giving him options.
Ideally, I agree with your approach. It is much easier to work with.

Phil

While this expression may work (may, because Nulls could mess with it) when
there are exactly three tries (as the OP stated), there will need to be a
major maintenance effort made if the number of tries ever changes. The
table will change, any queries referring to those fields will change, any
forms using those fields will change, any code/macros ... (you get the
picture).

In the long run, the OP will either pay now (normalizing the data) or pay
later (increased maintenance). Or, there's always Excel?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

You have try1, try2, and try3. Those are the fields with each score.

Use an Iif to give you the greater of try1 and try2, then use an iif to
give you the greater of that, or try3.


Iif (
iif([try1]>[try2],[try1],[try2])>[try3],
iif([try1]>[try2],[try1],[try2]),
[try3])

Phil

Thanks for the response Jeff,

I did it that way because the 3 trials are all tied to the same date. An
example would be that we ask a patient to perform a task 3 times and use
the highest score. But I need to capture the score of all 3 tests. Then I
would graph the improvement over time of the maximum scores.

If there is another way I am open.

Tom





Tom

That depends...

Are the three scores held in three separate fields in the underlying
table? If so, this describes ... a spreadsheet! Looking "across" fields
for a maximum is something that you'd do in Excel, but Access is a
relational database, and provides relationally-oriented features and
functions.

In a well-normalized Access table, finding the maximum of a set of scores
involves looking "down" (across multiple records), not "across" (within
the same record).

Regards

Jeff Boyce
Microsoft Office/Access MVP



I have a form that has 3 trial scores, I would like a field in the same
form to show me the maximum score of the 3 trials.

How can this be done?

Thanks, Tom

 

[Tom Tripicchio]

Thx guys for the direction.

I agree with the narrow and deep, but not sure on how to set up the form for
users that I have. Must keep it as basic as possible.

I will play with both and hopefully expand my wide and shallow knowledge.

Tom

Certianly, but he can get his stuff working quickly, and have a little
breathing room to make the more labor intensive changes to normalize his
database. To fix the nulls, he could wrap all of the [tryx] in a NZ().

Not suggesting my way is best, or yours is bad, just giving him options.
Ideally, I agree with your approach. It is much easier to work with.

Phil

While this expression may work (may, because Nulls could mess with it)
when there are exactly three tries (as the OP stated), there will need to
be a major maintenance effort made if the number of tries ever changes.
The table will change, any queries referring to those fields will change,
any forms using those fields will change, any code/macros ... (you get
the picture).

In the long run, the OP will either pay now (normalizing the data) or pay
later (increased maintenance). Or, there's always Excel?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

You have try1, try2, and try3. Those are the fields with each score.

Use an Iif to give you the greater of try1 and try2, then use an iif to
give you the greater of that, or try3.


Iif (
iif([try1]>[try2],[try1],[try2])>[try3],
iif([try1]>[try2],[try1],[try2]),
[try3])

Phil










Tom Tripicchio wrote:

Thanks for the response Jeff,

I did it that way because the 3 trials are all tied to the same date. An
example would be that we ask a patient to perform a task 3 times and use
the highest score. But I need to capture the score of all 3 tests. Then
I would graph the improvement over time of the maximum scores.

If there is another way I am open.

Tom





Tom

That depends...

Are the three scores held in three separate fields in the underlying
table? If so, this describes ... a spreadsheet! Looking "across"
fields for a maximum is something that you'd do in Excel, but Access is
a relational database, and provides relationally-oriented features and
functions.

In a well-normalized Access table, finding the maximum of a set of
scores involves looking "down" (across multiple records), not "across"
(within the same record).

Regards

Jeff Boyce
Microsoft Office/Access MVP



I have a form that has 3 trial scores, I would like a field in the
same form to show me the maximum score of the 3 trials.

How can this be done?

Thanks, Tom