Max value

  • Thread starter Thread starter Golfinray
  • Start date Start date
G

Golfinray

In a query, I have a table that contains 2003,04,05,06,07,08 enrollments for
all schools in the state. Also there are PROJECTED enrollments for 09, 10,
11, 12, 13.
I am trying to use Dmax or Max to get the highest of these 12 values so that
I can report what will likely be their highest enrollment over the next 5
years and whether this school district has increasing or declining
enrollments. The table is dbo_districtenrollment, and the columns are 03E,
04E, 05E, 06E, etc. Thanks so much!!!
 
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
 
Back
Top