 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / May 2004Adding Up Hours
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. 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.  SignatureDuane Hookom MS Access MVP -- > I need to track the number of hours a student accumulates > while taking various courses. These hours will then need [quoted text clipped - 9 lines] > > Thanks. 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. SignatureBrian 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. 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. >-----Original Message----- >This will add your times: [quoted text clipped - 23 lines] > >. 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. SignatureBrian 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. >-----Original Message----- >This will add your times: [quoted text clipped - 4 lines] > > 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. message >news:1377201c443fd$f0793a40$a401280a@phx.gbl... >I need to track the number of hours a student accumulates [quoted text clipped - 12 lines] > >. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.