Working with High and Low Values in Access Tables
I spend a lot of time building forms and reports around exam score tables where you need to track high and low values. People usually come to this because they want a quick view of who scored best and worst, or they're trying to flag students who are borderline. The concept sounds simple but Access makes it messier than it should be if you don't plan ahead. The most basic approach is to build a table that stores individual exam records and then use queries with DMax, DMin, or aggregate functions to pull out the high and low scores. Here is how I typically set it up. First, the table structure. You need a table called ExamScores with fields like StudentID, ExamDate, Subject, Score, and maybe a PassMark field. Keep the Score as a number type, preferably Double if you're dealing with decimals. Integer works too but you lose the precision. I have seen people make the mistake of storing scores as Text because they want to include grade labels like A or B directly in the same field. That breaks every aggregate function you will ever try to run on it. Do not do that.
Once the table is built, your first query should be a summary query using GROUP BY on StudentID with aggregate functions for the max and min scores. Something like SELECT StudentID, Max(Score) AS HighestScore, Min(Score) AS LowestScore FROM ExamScores GROUP BY StudentID. This gives you the basic high and low per student. Add more GROUP BY fields if you need it broken down by subject or exam date range. The problem most people run into is when they try to get the high and low across the entire class at once versus per student. The GROUP BY clause controls that behavior. No GROUP BY with aggregate functions gives you the whole class. Grouping by StudentID gives per-student. Grouping by both StudentID and Subject gives per-student per-subject. I spent three hours debugging a form last month because a junior developer had omitted Subject from the GROUP BY and the query was returning wildly wrong numbers for students who took multiple subjects. It looked fine at first glance because the scores were technically still the max and min of something, just not what anyone expected. For a more interactive solution, I usually build a form with a subform. The main form shows student information. The subform is a datasheet view of the ExamScores table filtered to that student. On the form header, I place text boxes that use expressions like =DMax("Score","ExamScores","StudentID=" & Me.StudentID) and =DMin("Score","ExamScores","StudentID=" & Me.StudentID). This updates dynamically as you navigate between students. The downside is that DMax and DMin requery on every navigation event, which gets slow if your table has thousands of records. If you are working with large datasets, switch to a recordset approach instead. Open a DAO recordset filtered to the current student, loop through it once, and store the max and min in variables. It takes a few more lines of code but cuts the lag from about half a second per navigation to nearly instant.
Another thing people overlook is handling NULL scores. If a student missed an exam or the score hasn't been entered yet, DMax will return NULL and your expression will break or show blank. Wrap your DMax and DMin calls with NZ() to convert NULLs to zero or to some placeholder value. Like =NZ(DMax("Score","ExamScores","StudentID=" & Me.StudentID), 0). But think before you do this. A missing score and a score of zero are very different things. In my experience, it is better to flag NULLs separately with a conditional format on the form rather than silently replacing them with zero. You can set the text box to display "Absent" or "Not Taken" when the underlying value is NULL. For reporting, I tend to avoid putting the high-low logic directly in the report and instead use a pre-built query as the report's record source. Reports recompute aggregate functions on every print or preview pass, and with complex filtering it can make the report take five to ten seconds to load when a query-based approach would take under a second. I measured this on a sample database with about eight thousand exam records and the difference was measurable. Pre-aggregated query: 0.8 seconds. Report-level aggregates: six seconds. That gap gets worse as the data grows. If you need to export the high and low data regularly, build a macro or VBA procedure that runs the summary query and exports it to Excel using DoCmd.OutputTo. I have a standard routine that runs every Monday morning and emails the results to department heads. Takes about twenty seconds to process and send. Worth setting up properly the first time rather than having someone manually export and email it every week.
Get the Full Details
There are scenarios where this whole approach falls apart. If your exam data comes from multiple systems and the StudentID is not consistent across them, all of the above breaks. You will get duplicate student entries and the high and low values will be wrong. The only real fix is a deduplication step before any of the queries run. I usually add a staging table where raw data lands first, clean it with a separate dedup query, and then move the cleaned data into the main ExamScores table. It adds a step but it stops the silent data corruption that happens when IDs mismatch. The other edge case is when you need high and low values that are time-sensitive. Like the highest score in the last thirty days versus the all-time high. DMax does not have a built-in date filter that is easy to use inside a form expression. You either write a custom function or you create two separate summary queries with date criteria and link them to your form. I wrote a small function called fHighLow that takes the table name, field name, student ID, and an optional date parameter. It builds the SQL string dynamically and returns the result. One function replaces about twelve different DMax calls and keeps the form design clean. If you want the raw files to work with, you can find sample databases online by searching for Access exam score template or similar terms. Microsoft's own template gallery used to have one but it has been pulled from recent versions. Third-party Access template sites still carry basic versions. Just be careful with macros in downloaded .accdb files. Disable them until you inspect the code.
