using MAX function in a calculated field

[parnassus1]

I have a database that holds records for pupils' test scores. Can I use the max function in a calculated field to look across several fields (i.e test1, test2 ect) and list the field with the highest score entered?

 

[Pat Hartman]

No. Aggregate and Domain functions in relational databases (not just Access) always work on a single column (or expression) for all rows of the recordset. Similar spreadsheet functions work for either column or row ranges. The difference is that in a spreadsheet, each cell is an independently addressable object. In a relational table columns are indivuadly addressed but rows are operated on as a set.

Your problem is that your table is designed as if it were a spreadsheet rather than a relational table. You have a 1-to-many relationship between pupil and test score. The test scores belong in a separate table where they can be properly managed. If you don't wish to restructure your table, you would be happier using it in Excel than in Access.

 

[dcx693]

Pat is absolutely right. Your database is best managed in something like Excel if you wish to have it structred the way you do.

Ideally, one of the best ways to hold students and test scores is to have a table of students, a table of tests, and a joining tables which relates the students to the tests for test scores.

So ideally, your test scores table would look something like this:

StudentID TestID TestScore
---------- ------ ----------
    1        1       85
    1        2       87
    2        1       90
    2        2       83
    2        3       85

That way, finding the max test score per student would be easy. Just use a totals query with a max function grouped by student ID.

If your database is already firmly entrenched and you cannot change the structure (which I encourage you to do), I've written a custom function which will get you the number you want. It ain't pretty, but it works. You call the function MaxTestScore from a select query and you pass it the ID number of a student. It assumes 4 tests have been given, but you can easily change that. It also assumes this table structure:

StudentID
Test1
Test2
Test3
Test4

Here's the function:

Function MaxTestScore(intStudentID As Integer) As Double
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim intX As Integer
Dim intNumTests As Integer
Dim dblMax As Double


  strSQL = "SELECT * FROM tblTestScores " & _
    "WHERE StudentID=" & intStudentID
  Set dbs = CurrentDb()
  Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)

  intNumTests = 4 '- Fill in your actual number
  dblMax = rst!Test1
  For intX = 2 To intNumTests
    If rst("Test" & intX) > dblMax Then
      dblMax = rst("Test" & intX)
    End If
  Next intX

  MaxTestScore = dblMax

  rst.Close
  Set rst = Nothing
  Set dbs = Nothing

End Function

Type the function into a VB module and call it from your select query using this type of expression:
High:MaxTestScore([StudentID])

 

[raskew]

Pat-
Agree with you totally. However, the enquiry raises an interesting question. Say the table looked like:

StudentID	    Test1	Test2	Test3
1	              95	88	72
2	              74	90	86
3	              89	73	100

You could use this little function (in a calculated field) to return the highest score in each row.

Function MaxOfSet1(ParamArray varMySet() As Variant) As Variant
Dim n As Integer, MaxHold As Variant
'check for entry
If UBound(varMySet) < 0 Then Exit Function
MaxHold = varMySet(0)
For n = 0 To UBound(varMySet())
   MaxHold = IIf(varMySet(n) >= MaxHold, varMySet(n), MaxHold)
Next n
MaxOfSet1 = MaxHold
End Function

You'd call it like this:

SELECT tblGrades2.StudentID, MaxOfSet1([Test1],[Test2],[Test3]) AS Expr2
FROM tblGrades2;

What I got wrapped around the axle about was trying to return the field name, e.g. Test1, Test2, etc. that the function was currently addressing. Any thoughts?

Bob

 

[Pat Hartman]

Bob, I know you are looking at this as an intellectual exercise. It's a rainy, boring Saturday afternoon here also and I am tempted but I'm not joining in. I admire your intellect and I'm sure you will come up with a clever solution. But, I would categorize this problem to be in a class of things along side the media's willingness to call homicide bombers suicide bombers. Doing so just plays into the hands of the wrong thinking people and gives their problem credence. Call me arrogant, pig-headed, stodgy, a dinosaur but I really believe that doing it "right" works best in the end. This is just another one of those pay me now or pay me later problems. The poster can take this opportunity to fix his data structure to resolve his current and future problems and learn something that will stand him in good stead. Your solving his problem by creating clever code that he is not capable of creating for himself doesn't teach him anything and won't help him to avoid the same mistake in the future. Nor will it help other members reading this thread.

BTW, maybe my letter writing campaign is having an impact. Fox news is now calling these misguided people homicide bombers. Sorry if the political commentary offended anyone.

 

[dcx693]

Bob, we had a rainy boring Saturday morning here in New York also, so that's why I took the time to write my function. (Your idea is better though, since you might as well just pass the test scores instead of retrieving the scores in code. Clearly, I can benefit from reading and well as writing to these boards.)

Pat, I don't condone bad database design, but going through these intellectual exercises sometimes benefits us.

We sometimes inherit databases that are singular in their task and function. That's not to say that needs never change because they often do. Sometimes you just need to complete a single task and you don't have the time or the inclination to do it "totally right".

I've created databases where I've not taken the time to use standard naming conventions. I've edited databases where I've not bothered to fix the bad design. The point of using the database in the first place is to get answers. Your boss typically wants them yesterday and he doesn't care that you've joined the proper tables using referrential integrity. He just wants to know the answer. You should always use the proper tool in the proper way, but sometimes getting the job done sooner is more important.

I've seen the problem from the user side and from the programmer side. You have to balance current needs with future possible needs.

The user who posted the original question could be in a number of situations. Perhaps he/she is just a lazy person who thinks it will be cool to do something in Access instead of that boring Excel that everyone else is using. Maybe it's a secretary in a school who got a database from someone else and just wants to get the answer so they can get something out. I don't know.

I just try to help people along while pointing them in the direction I think they should proceed in. Maybe I'm wasting my time, but I like it when people waste their time like that on me. If someone were to help me with a problem yet push me in the better direction at the same time, just maybe I would listen if I had the desire to better myself. I wish in the business world we all had the proper time, but sometimes we don't.

Let see what type of response the original poster gives us. Hopefully we haven't scared him/her off!

Sorry for the rambling. As I said, it really is a raining boring Saturday morning - er, afternoon. Just my two cents.

 

[parnassus1]

reply

Thanks for all the help and discussion. Basically the problem is that I help record keep for my department in the school. Talking about 1000 records. I know the data needs normalised and understand the process BUT didn't take pupil test scores into different tables because it would involve an extra hurdle every time I entered new test scores for pupils. The problem being entering pupil number etc. This would be a task that I do, not as part of my job but just in passing. Considering the number of records involved, it just wouldn't be worthwhile normalising these tables. So perhaps I stand guilty of bad data design but I made a trade off decision and balanced up the fact that finding the highest score was a nice extra rahter than the basic principle upon which my data needed to be held. I had a solution (probably bad but it worked) with nested ifs ands ors etc.

thanks again for the help and the comments.

 

[dcx693]

parnassus1,
Please tell us more about the structure of your database.

Will each student have a maximum number of tests? Or will it be variable? An how do you identify students in your database? Do you use an ID number or name?

There is no way to easily compare values in multiple different fields using a query. That's why raskew and I both suggested custom functions.

If each student has a maximum number of tests and it's small, I'd use a variation of raskew's technique, pass all the test scores to a custom function to find the max. (Unfortunately, Access does not have a built-in max function.)

If each student can have any number of tests, I'd use the solution I posted above.

 

[Rich]

Unfortunately, Access does not have a built-in max function

Surely it does.

 

[dcx693]

Rich, surely Access supports the SQL aggregate max function, but can you just call a Max() function from a select query or in VBA code? If you can please tell me how.

 

[parnassus1]

Thanks for all the extra help...answer is that there are fixed number of tests. Basically, I am reocrding tests for nearly 1000 pupils over the 6 year period that they are in school so only recording main tests or exams. Having said that, not all pupils will take all exams..course choices etc so I know it should be normalised but it makes data entry too awkward. I am using an auto number as primary key. I had one plan to combine pupil names and autonumber as the field relation(if i was going to normalise fully) but it was just too difficult for other people to use not to mention annoying trying to select a pupils name from a list of 1000. The thing is it would have added a horrific complication having to use numbers to identify pupils.

so simply, I need to find the maximum grade over pupils work for some years of their course. Not talking about any more than 3 different tests involved in this process so would have been nice if I could have easily used a predefined function to find the max across the same record. I dont have a big problem with writing VBA code to do that..(despite the comments made above)just wondered if access could do it but now i understand why not.

I can't really go the spreadsheet route because I want reports and forms etc for other very non computer minded people to use.

Thanks to all for the solutions. Please bear in mind that I am combining paper systems and a database that I made on a Mac over 10 years ago now......so anything is progress.

 

[Rich]

Aren't you using Access as a spreadsheet anyway?

 

[dcx693]

parnassus1, since there are a fixed number of tests, you could include a column in your query that looks like this:
HighScore([Test1],[Test2],[Test3],...etc)

That's assuming you have a mangageable number of tests, though you'd only have to type that equation one time. That solution utilizes a HighScore custom function. If you're interested in going that route, let us know and we can help you write it.

That said, I'm confused about some of the things you've written. When you're using an "auto number as primary key", if you're not using it to indentify the students, then for what? For each test? You say that normalizing your tables "makes data entry too awkward". I disagree absolutely with that statement. Normalizing tables makes data entry the most straightforward it can be. You say that it was it was annoying to "select a pupils name from a list of 1000". Well, how are you associating a test with a student now then?

It sounds like you don't really understand relational database systems. I don't want to sound like your mother, but if you take the time to learn more about database design, you'll understand why some of the other people in the thread suggest that you are going about the problem in the "wrong" way. Trust me and them when we say that if you take the time to solve this problem the right way, you will be better off for it in the long run. (That's my gentle way of pushing you in the right direction.)

 

[parnassus1]

In answer to am i using this as a spreadsheet anyway...definitely not. Have linked tables for class details and teacher details which are simple to update. Using forms for data entry split up by year group. Some of the people using this database have no computer training and I would not let them near a spreadsheet of 1000 rows and 50 odd columns..protected cells or not. Would be far too ungainly to use. The reports that access can generate suit me fine for analysing data across a whole year..eg how many pupils got an "A", how many boys got a "B" etc. Would be far more complex to do this in excel. So am using access for ease of data entry and output and some features of its relational capabilities (but not to third normal form as i accept). However the database, as it is, is easy to use and get the data I need out of so its design suits the user and not the "programmer". However often I am both so I see it from both sides. Essentially it is not a commercial database. Just helps me record grades amongst the 1001 other things I have to do in a day.

 

[raskew]

As in:

"You can lead a horse to water but you can't make him drink."

Oh well. Pat, as it turned out, you were 100% 'on track'. (Damn-it pains me deeply to say that but have to admire your insight!)

Best wishes,

Bob