Calculate total time (2003)

[MelanieB]

I was asked to total the number of hours each employee spent taking each class.
Some classes span a couple of days or a week - most are just a few short hours. What the requestor wants is a list of the student names, then the classes he/she took listed (once for each class) and the total number of hours he/she spent in those classes. I'm at a loss. I made a calculation to show the number of hours in each class. But, when classes span more than a day, I get confused. And, when I try to group the classes (for those people who took the class multiple times), then all my calculations are wrong. Can you help me please?

[HansV]

Could you provide a sample of what the data look like?

[MelanieB]

Here it is. Thanks, Hans. I put the sample data in an Excel spreadsheet because I had to modify the names in it for confidentiality. This is a direct pull from a query (with just the student names changed). I can send the db offline if that will help, but I can't post it. Thanks.

[johnhutchison]

Can you explain what a line like this means?
Did they teach the class every week for 10 months? Were there any holidays?

[MelanieB]

These are students attending classes .. some classes are one day, some are a week long, and some are for 3 - 6 hours a week for several months. On this particular line, I know she went to this class two nights a week for several months, but I'm not sure how many hours each night she went. For argument's sake, let's assume she went 6 hours a week for 12 weeks = 72 hours of training time.

I have a similar database that tracks computer training and I listed each session separately. Should I have her do that with her database? It's easier for me to calculate time with each session separately. But, she has something like 600 records and she didn't want to modify it. However, I don't see any way around that.

[HansV]

The table you posted doesn't contain any information about how many days the person actually attended a course, so it is not really possible to compute the total duration.
It would be easier to use a table that contains a record for each session separately.
Otherwise there should be a table that provides detailed information about a course, e.g. how many sessions it had.

[johnhutchison]

Sorry for my misunderstanding. In Australian English (as I use it anyway) to take a class is to teach it.

Access can't perform the calculation unless all the required info is present in the database.

Total Hours is : [Number of weeks] x [classes per week] * [hours per class].

You could calculate the number of weeks using the DateDiff function.
Number of weeks : DateDiff("ww", [Start Date], [End Date]) +1

the DateDiff function with ww actually calculates the number of Sundays between the two dates, so I would expect the result to be one less than what you want.