Finding out when students begin and end their school days based on class schedule records

[ndanty]

Hello,



I am not well-versed in Access so I will try to make this as simple as possible.

Project goals: Create two tables with column headings for each day of the week and row headings for specific time intervals (7:00am - 8:30 am, 9am - 10:30 am, etc). The first table will show class start times at a university, and the second table will show class end times. Each cell within the matrix will show how many students begin/end their first/last class within the specified time interval. (Example: Johnny takes 2 classes on Monday with the first one beginning at 8:00 am, and his second one ending at 3PM. Thus, he will show up in the 7-8:30 am interval on Table 1, and the 3-4:30PM interval on table 2.)

Data available: My boss gave me two spreadsheets to work with
1) Student ID# and class number
2) Class number, days of the week which the class occurs, and start/end times for each class
3) Class# and Section #

Definition of data:
1) Student ID is a unique identifier given to every student at the university
2) Class number is the identifier given to a university class (i.e Creative Writing = Class Number 47096)
3) Section # is an identifier representing a specific meeting time for a given class (i.e Creative Writing = Class Number 47096, which is then split into sections 1 and 2 (1=8:00 am meeting time, 2 = 1pm meeting time)

Problems:
Because the Class Number is not unique to every section, when I try to merge the spreadsheets to show Student ID and meeting time, students are showing up as being enrolled in multiple sections of the same course. How can I fix this to get a spreadsheet that shows Student ID and meeting time only for the section that the student is enrolled in?

Additionally, if/when I get to this point, how can I tell that a student is beginning his/her FIRST class at a given time interval, and not simply their second or third class? I don't want to double or triple count students because they have fallen into multiple time interval categories throughout the day. In the end, each student should only have 2 records per day: The time they begin their first class and the time they end their last class.

My supervisor has been up my ass about this despite the fact that he knew full well that this is not my background. I have had programmers around campus tell me varying methods for figuring this ouot using complicated jargon, coding language and a slew of other things unknown to me. I hope there is just one step that I'm missing. Any help would be greatly appreciated.

Thanks,
Nick

[Gina Maylone]

Would you mind posting your database? Of course, remove any sensitive information. Thanks!