Show Maximum number from multiple Fields in a query

[Keenan]

I have a student room utilisation database. Actual numbers of students are
entered into the db by Room/Class/Date/Time period. ie:

Room - Class - Date - 8amto9am - 9amto10pm - 10amto11am
A101 - PC123 - 29/10/08 - 10 - 13 - 20
A102 - PC111 - 29/10/08 - 20 - 21 - 23
A103 - PC222 - 29/10/09 - 25 - 25 - 24

I want to have a field in a query that will show the Max number across all
time periods for that record. ie. the above should show:
Room - Max
A101 - 20
A102 - 23
A103 - 25

Hope you can help. Thanks


James

 

[John W. Vinson]

I have a student room utilisation database. Actual numbers of students are
entered into the db by Room/Class/Date/Time period. ie:

Room - Class - Date - 8amto9am - 9amto10pm - 10amto11am
A101 - PC123 - 29/10/08 - 10 - 13 - 20
A102 - PC111 - 29/10/08 - 20 - 21 - 23
A103 - PC222 - 29/10/09 - 25 - 25 - 24

This is a good spreadsheet design... but an INCORRECT database design. A
correctly normalized structure would be tall and thin, with fields Room,
Class, ClassDate (don't use Date, it's a reserved word), ClassTime, and
ClassSize; each of your rows above would correspond to three rows in the
normalized structure.

I want to have a field in a query that will show the Max number across all
time periods for that record. ie. the above should show:
Room - Max
A101 - 20
A102 - 23
A103 - 25

You can write a VBA function or a complex query... or normalize yor data and
do a trivially simple totals query grouping by Room and selecting max of
ClassSize.

 

[Keenan]

John

Thanks for the quick response. I understand the database isn't designed the
best. However, the database input is relatively complicated as it takes 2
different forms and data, compares them and reports pretty well.

It uses:
1. Actual student numbers for each room in 1hr blocks which is manually
counted by security guards and recorded in the format:
Room - Class - Date - 8amto9am - 9amto10pm - 10amto11am
A101 - PC123 - 29/10/08 - 10 - 13 - 20

2. The Schools Celcat timetable system exports a report that shows all the
timetabled classes running ie.
Room - Class - Date - Start time - End Time
A101 - PC111 - 29/10/08 - 9:00am - 5:00pm

It reports in a number of ways but it basically shows utilisation of the
Institute's 2,500 rooms by actual room capacity vs audited students,
timetabled hrs vs actual hrs used. It highlights those timetabled classes
that were booked into a room and not actually used. It reports everything we
want but just can't get this Max to work ie.

Room A101 (between 8am - 5pm)
Day - Utilisation % - Avg # of students - Max # of students
Mon, 20/10 - 30% - 10 - ????
Tue, 21/10 - 40% - 15 - ????
Wed, 22/10 - 50% - 20 - ????
Thu, 23/10 - 22% - 8 - ????
Fri, 24/10 - 10% - 5 - ???

I only have basic knowledge of Access, so any help is appreciated.

James

 

[Douglas J. Steele]

If you have to leave the tables as they are, so be it. You can use a query
to transform the data into the properly normalized format, which will be
easier to achieve the results you want:

SELECT Room, Class, [Date], "8amto9am" As TimeRange, 8amto9am As
StudentCount
FROM MyTable
UNION
SELECT Room, Class, [Date], "9amto10am" , 9amto10am
FROM MyTable
UNION
SELECT Room, Class, [Date], "10amto11am" , 10amto11am
FROM MyTable
....
UNION
SELECT Room, Class, [Date], "4pmto5pm" , 4pmto5pm
FROM MyTable

 

[John Spencer]

As John Vinson noted your structure needs to be revised. Data display does
not have to be the same as data storage.

If you cannot revise your structure at this time you will need a custom VBA
function to get the maximum value. See below for a function you can paste
into a VBA module and call in a query.

Field: MaxNum: fRowMax(8amto9am,9amto10pm,10amto11am, ...)

Allen Browne has similar functions on his site - http://allenbrowne.com

'------------- 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
DIM 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
The Hilltop Institute
University of Maryland Baltimore County