Trying to calculate tutor hours when student classes overlap

[SlowLearner]

Using Access 2003. I am trying to calculate the total of hours worked by
tutors by summing the totals from the timetable. The problem arises if a
tutor is timetabled in room A on Monday between 1 and 3, and also in room A
on Monday between 1 and 2 - a straight sum will double count the hour between
1 and 2.
Further complexity arises when
a) the courses do not start and end at the same time of the year
b) the classes may overlap when class 1 is from 9 to 11 and class 2 is from
10 to 12 - I need to calculate that as 3 hours of tutor time.

 

[KARL DEWEY]

It would help if you posted how you collect your data - table names and
fields. Then add a sample of data.

 

[SlowLearner]

There is a table called MainRegister which has Start and End Dates and Start
and End Times for each class. This table also has a field called
EventNumber. This links to an EventsDetails table which has a Type field.
This field can be of Type 'Person' or 'Room'. I have created a query that
gives me the data of:
Record No. 1: PersonCode 54321, StartDate #05/01/07#, EndDate #30/03/07#,
StartTime 09.00, EndTime 11.00, Room SG01.
Record No 2: PersonCode 54321, StartDate #05/01/07#, EndDate #30/03/07#,
StartTime 09.30, EndTime 11.00, Room SG01

Hope this helps

 

[KARL DEWEY]

Your data collection is flawed.

The data you present has the tutor there for 2 hours a day for 84 days or
168 hours. I do not think that they were there every day of the week.
Or were they there only on Fridays? Not sick, holiday, or vacation?

 

[SlowLearner]

I can calculate the day of the week when the class occurred, that is why I
did not put that info into my last post, apologies if this has confused you.

 

[KARL DEWEY]

I can calculate the day of the week when the class occurred,
Using the information you post as all that was collected you can not know if
the tutor worked 1, 2, or 5 days a week.
Or does your tutors ONLY work one day a week, no holidays, and no other
absences?
If this is the case then just use DateDiff function.
DateDiff("w", [StartDate], [EndDate])+1 * DateDiff("h", [StartTime],
[EndTime])

If you can not get this to work for you then post the actual table
structures with names and date types. Also post sample data from each table.

 

[SlowLearner]

There are multiple tutors, teaching many classes a day, over 5 days. Most of
the tutors have one class for an allotted amount of time (EndTime - Start
Time) for an
allotted number of weeks (EndDate - StartDate). They are not the problem.

Some tutors teach more than one group of students in the same room during
the same time slot , therefore on the Main Register table they would have the
2 records outlined earlier for the same time slot.

One group starts at 9, and the second group starts at 9.30. If I just count
the hours from the tables, I show that tutor as working for 3.5 hours (2 +
1.5), which is wrong.

Any thoughts ?

Using the information you post as all that was collected you can not know if
the tutor worked 1, 2, or 5 days a week.
Or does your tutors ONLY work one day a week, no holidays, and no other
absences?
If this is the case then just use DateDiff function.
DateDiff("w", [StartDate], [EndDate])+1 * DateDiff("h", [StartTime],
[EndTime])

If you can not get this to work for you then post the actual table
structures with names and date types. Also post sample data from each table.
--
KARL DEWEY
Build a little - Test a little

I can calculate the day of the week when the class occurred, that is why I
did not put that info into my last post, apologies if this has confused you.

 

[KARL DEWEY]

You just repeated the same information posted earlier. If you want
suggestions how to do this then do as I asked and post the actual table
structures with names and date types. Also post sample data from each table.

 

[SlowLearner]

I repeated the earlier information because your previous post led me to
believe that you did not understand the problem, your answer calculated the
hours for an individual record, which I could do anyway. Thanks for trying
to help, I will output the query into Excel, and use a macro there to
calculate the hours.