Your structure is wrong. You should have
SchoolDistrict
SchoolYear
Enrollment
You might be able to fix this by using a union query to restructure the data
and then using the union query as if it were a table
SELECT SchoolDistrict, "03E" as SchoolYear, 03E as Enrolled
FROM dbo_DistrictEnrollment
UNION ALL
SELECT SchoolDistrict, "04E" as SchoolYear, 04E as Enrolled
FROM dbo_DistrictEnrollment
UNION ALL
SELECT SchoolDistrict, "05E" as SchoolYear, 05E as Enrolled
FROM dbo_DistrictEnrollment
....
UNION ALL
SELECT SchoolDistrict, "13E" as SchoolYear, 13E as Enrolled
FROM dbo_DistrictEnrollment
Now you can get various things fairly easily
SELECT SchoolDistrict, Max(Enrolled)
FROM TheUnionQuery
GROUP BY SchoolDistrict
Or to get all the information at one time.
SELECT *
FROM TheUnionQuery
WHERE Enrolled =
(SELECT Top 1 Enrolled
FROM TheUnionQuery as T
WHERE T.SchoolDistrict = TheUnionQuery.SchoolDistrict
ORDER By T.Enrolled Desc)
If you can't fix the data, then you are going to need a VBA function to get
the max number from the 11 fields involved. Here is one that I wrote long ago.
SELECT schoolDistrict, fRowMax(03e,04e,...13e) as MaxEnrolled
FROM dbo_DistrictEnrollment
'------------- Code Starts --------------
Public Function fRowMax(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call: myMax = GetMaxNumber("-21","TEST","2", "3",4,5,6,"7",0) returns 7
'Ignores values that cannot be treated as numbers.
'
' Max of 29 arguments can be passed to a function in Access SQL
' workaround is to nest fRowMax calls for groups of fields.
Dim i As Integer, vMax As Variant, tfFound As Boolean, dblCompare As Double
vMax = -1E+308 'very large negative number
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare > vMax Then
vMax = dblCompare
tfFound = True
End If
End If
Next
If tfFound Then
fRowMax = vMax
Else
fRowMax = Null
End If
End Function
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
