If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Finding Maximum Score
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 |
#2
|
|||
|
|||
Finding Maximum Score
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" wrote in message ... 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 |
#3
|
|||
|
|||
Finding Maximum Score
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" wrote in message ... 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" wrote in message ... 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 |
#4
|
|||
|
|||
Finding Maximum Score
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 "Tom Tripicchio" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#5
|
|||
|
|||
Finding Maximum Score
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 "Jeff Boyce" wrote in message ... 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" wrote in message .. . 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 |
#6
|
|||
|
|||
Finding Maximum Score
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 "Phil Smith" wrote in message ... 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 "Jeff Boyce" wrote in message ... 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" wrote in message . .. 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 |
#7
|
|||
|
|||
Finding Maximum Score
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. Jeff Boyce wrote: 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 "Phil Smith" wrote in message ... 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 "Jeff Boyce" wrote in message .. . 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" wrote in message ... 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 |
#8
|
|||
|
|||
Finding Maximum Score
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 "Phil Smith" wrote in message ... 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. Jeff Boyce wrote: 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 "Phil Smith" wrote in message ... 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 "Jeff Boyce" wrote in message . .. 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" wrote in message l... 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 |
Thread Tools | |
Display Modes | |
|
|