summing a time field across records

[ken_s2007]

Apologies for what is a basic question, but I'm trying to sum one time field across a set of related records (e.g., all children of a a related parent record) and have tried various calcs and summaries, etc. but have come up short. I've read the FM help and have looked through my 3rd party books as well. If nothing else, could someone tell me if I need to convert the data in the time fields (hours, minutes, seconds) to numbers first, and then convert back to a time format for the summary? Thank you.

Ken

Edited April 26, 2006 by Guest

[comment]

No conversion is required. Define a calculation field in the parent table (result is Time) =

Sum ( Child::Timefield )

[ken_s2007]

Thanks, Veteran. Turns out I had it right, but... the time field I created for the children had a HH:MM format, and the sum field in the parent a HH:MM:SS format. So I was entering 2:03, thinking that was two minutes three seconds, but it was hours. I thought the value in the sum field was summing all the records (not just the related children). Got that sorted out now.

Users of this system will almost always enter values of minutes and seconds; occasionally seconds only, and *very* rarely any hours (excluding "0" of course). I wonder what a good interface would be; perhaps create number fields for hours, minutes, and seconds, then the user enters what s/he needs (e.g., for 2 min. 3 sec., enter nothing in the hour field, "2" in the minutes field, "3" in the seconds field, then some mechanism to set an actual time field. This way they'd have to enter no leading zeros or a time separator ( ":" ).

Ken

[comment]

I would use a text field for the entry and the period as the separator. Then the timefield can be a calculation (or auto-entry) of something like:

Substitute ( Right ( "00.00.00" & TextEntry ; 8 ) ; "." ; ":" )

(If you'd always enter only mm.ss, you could enter directly into the time field, and use an auto-enter to substitute a colon for the period. Unfortunately, it seems this won't work when the entry contains 2 periods.)