Adding Up Hours

[Jackie]

I need to track the number of hours a student accumulates
while taking various courses. These hours will then need
to be added, by query, to produce a total number of hours.

My problem is that I can't use a number field because then
I'll get results like 6:53 + 1:27 = 7:80 and not 8:20.

How do I get access to recognize that anything over 60
minutes needs to be added to the hours? I've tried
changing the format of a date/time field to h:nn but that
doesn't work either.

Thanks.

 

[Duane Hookom]

Store the number of hours as a number and not a date/time. 4 hrs and 30
minutes should be stored as either 4.5 hrs or 270 minutes.

 

[Brian Kastel]

This will add your times:

Format(TimeValue("6:53") + TimeValue("1:27"), "h:nn")

but you will need this if your total runs over 24 hours:

DateDiff("d", #0:00#, TimeValue("6:53") + TimeValue("1:27"))

which you can then multiply by 24 and add to the hours part of your total,
if you wish.

--
Brian Kastel


--Original Message----------------

I need to track the number of hours a student accumulates
while taking various courses. These hours will then need
to be added, by query, to produce a total number of hours.

My problem is that I can't use a number field because then
I'll get results like 6:53 + 1:27 = 7:80 and not 8:20.

How do I get access to recognize that anything over 60
minutes needs to be added to the hours? I've tried
changing the format of a date/time field to h:nn but that
doesn't work either.

Thanks.

 

[Jackie]

Does this mean I have to type
Format(TimeValue("6:53") + TimeValue("1:27"), "h:nn")
for every single record????? This seems very time-
consuming.

 

[Brian Kastel]

Er... no. That wouldn't work, anyway. Besides, Duane's suggestion of
storing the hours as a Single was better, as you can always convert the
fractional part to minutes in your reporting.

Set up a Students table and an Attendance table separately, with the
attendance table containing records for each date of attendance along with
the hours (3.5, say) attended for that date. You can then run a total query
for all students showing the total number of hours attended. If you have
more than one class (likely), then you might also want to create a table
with the details for your classes. Your Attendance records would then
consist of foreign keys to Students and Classes, as well as the date and
hours information.

--
Brian Kastel


--Original Message----------------

Does this mean I have to type
Format(TimeValue("6:53") + TimeValue("1:27"), "h:nn")
for every single record????? This seems very time-
consuming.