Off the top of my head, and untested.... this sounds like a problem
of database design. Here is how I would approach it:
tblTimeSlots
- a table to list all the available timeslots
- one record per timeslot
- starting time of each timeslot
- assumed a consistent length of 30 minutes per timeslot
- eg. there would be 12 records to account for the Mon, Wed, Fri 1-3
timeslot
tblClasses
- one record per class
eg. the Mon, Wed, Fri 1-3 class would occupy one record
tblClassesTimeslot
- to record what timeslots are occupied by each class
- one record per class and timeslot
- eg. the Mon, Wed, Fri 1-3 class would occupy 12 records
- the Mon, Tues, Wed 2-4 class would use 12 more records
tblStudents
- one record per student
tblClassRegistration
- one record for each student in each class
With this structure, you could create a query that shows each class
that each student is registered for, and each timeslot occupied by
those classes. From there, you collapse the query with the Sum
function to count the number of students registered for each timeslot.
But it starts with the correct table structure.
The only field that needs to be Date/Time type is the beginning time
of each timeslot.
Scenario: 2 students in class on Mon, Wed, Fri from 1-3, another 3 students
in class Mon, Tues, Wed from 2-4. I would like a report/query that displays
in half hour blocks the sum of students in that timeframe.
For example above result would be
Monday 1-1:30 = 2
Monday 1:30 -2 = 2
Monday 2-2:30 = 5
Monday 2:30-3 = 5
and so on.
How should I create the "classes" table? Do I use date/time fields (Medium
Time), check boxes, or numeric values?
**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
