Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculate total time (2003)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculate total time (2003)

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

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate total time (2003)

    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.
    Attached Files Attached Files

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Calculate total time (2003)

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



  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate total time (2003)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculate total time (2003)

    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.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Calculate total time (2003)

    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.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •