short time total hh:nn

[Chris121]

Hi Folks
I have a short time field in a select query and need to show a total of the time field when the query is run.
I tried using that greek symbol to make it into a totals query and selected "sum" in the totals row, but that must be wrong, as I get weird results for each record in the query, wheras I only want to show one total for ALL records in the query.
I thought this must be a fairly common question type, but having looked in FAQ's and searched through old threads, I'm no further.

any help, tips or advice apreciated,

Christine.

 

[lespaul]

Doesn't the short time field record the time in hours and minutes? What are you trying to sum? Do you have a second time field that you are trying to get the difference between? What use would it be to add up 1:30 pm and 5:15 pm?

 

[Chris121]

The field I'd like a sum for is elapsed time equal to the ammount of time a football players heart rate is in the "Red Zone" (over 85% of max heart rate)
Red Zone mins are entered after each training session, and I would like the query to show the total hh:nn spent in the Red Zone in a given time period.
I have Player name and session date in parameter (criteria) to allow the physio to enter the date range and player name, but I'm unable to show a total for the redzone mins field.
please help!
Christine.

 

[hoggbottom59]

Date and time storage and use isn't straight forward!

A clue to how to solve this problem maybe to look into the underlying way that date/time is stored.

Every occurrence of a date or time field is actually stored as both a date and time.

e.g. 13:00:00 16-10-02 even if this is only displayed as 13:00 or 16-10-02 both parts are always there.

To confuse matters this is actually stored as single number in Access's eyes (take a look at the help), but as Access knows it's a date/time field it is displayed as thus.

There is no easy way to sum dates and times as they aren't divisable by 10. In my database I've hour measurements to use 0.25 for 1/4 hr intervals so I've made calculations easier.

There are a few date and time functions like DATEDIFF() you could explore. This returns a value rather than a date or time so you'd need to then convert it back to Date/Time.

Hope this helps towards your work,
Leon.

 

[Chris121]

Thanks for the input Leon. I managed to work out that Totals queries depend upon the way the other fields are grouped, and Access will add hh:nn fields and give totals if the total field has the same format. It's working ok for me anyway.
I recall doing an XL sheet to calculate an amount of time (early or late) from due Date/Time and Arrival Date/Time. XL (Much as you describe for Access) converts a Date/Time field into a number with figures before the decimal point indicating Date and those after the point indicate the time) The numbers can then be turned into calculated time columns using a simple + or - math. The only caveat being that time cannot have a negative value. Does Access use the same method for producing the Date/Time number?

 

[hoggbottom59]

Glad you've sorted it.

The following is an extract from the VB ref help thing in Access for the Date Data type -

Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers that represent dates ranging from 1 January 100 to 31 December 9999 and times from 0:00:00 to 23:59:59. Any recognizable literal date values can be assigned to Date variables. Date literals must be enclosed within number signs (#), for example, #January 1, 1993# or #1 Jan 93#.
Date variables display dates according to the short date format recognized by your computer. Times display according to the time format (either 12-hour or 24-hour) recognized by your computer.

When other numeric types are converted to Date, values to the left of the decimal represent date information while values to the right of the decimal represent time. Midnight is 0 and midday is 0.5. Negative whole numbers represent dates before 30 December 1899.

I expect it's exactly the same as in Excel.

Yours,
Leon.