Summing Time in Report

[billybeer]

I have looked around and I don't understand the posts I have found so....



I have a report (rptWeeklyStats) that uses a query (qry_WeeklyStats) as Record Source. The query pulls the time spent reviewing different videos from the main Table (tblMain). The table stores this in a column (TimeSpent) as a Date/Time Data Type.
In the Report Footer I am trying to sum the total time spent reviewing videos per week. Summing time does not seem to work. Can I do this with a formula in the report or do I have to do something in the query or the table?

[Minty]

You probably shouldn't store the result of your original calculation.
The general rule of thumb is if you can calculate it don't store it, as if any of the underlying values change you have to update your stored value.

Your problem is that a date time field will store exactly that a date and a time. You probably need to do your all calculations in the smallest denominator you need - let's say minutes.
So assuming you have a start and end field your would get the single line total in minutes in your query as LineMins: DateDiff("n",[StartDatime], [EndTime])

On your report you display this as a number in minutes, or you can do some sums to display it in Hours and minutes. The important bit is that the underlying minute values will still be available.
Sum those and then again you have a correct value in minutes that you can format into hours and minutes.

[billybeer]

is there a way to do this without start / stop fields. The time is entered into the table by a form that I would like the user to just be able to enter a total time spent on viewing the videos in one field instead of having them to put in start times and stop times.

[Minty]

Yes of course - you would need to decide what value they enter. Then the same advice applies.
Bear in mind that if they enter it as hh:mm (lets say 2:30) into a date field, then what is actually stored is 31/12/1899 02:30:00.00000

Dates are store as Double precision numbers, the integer represents the day the fraction the time.

This is why adding dates up can get "Interesting"

[davegri]

Probably best to have the form separate the hours and minutes data entry fields and treat them as numbers, not date/time. That would make any elapsed time calculations much easier.

[ssanfu]

<snip> The query pulls the time spent reviewing different videos from the main Table (tblMain). The table stores this in a column (TimeSpent) as a Date/Time Data Type.
<snip>

<snip> The time is entered into the table by a form that I would like the user to just be able to enter a total time spent on viewing the videos in one field instead of having them to put in start times and stop times.

I see this often. There is a misunderstanding or a lack of understanding between Times and Durations.
You say there is a column (field) named "TimeSpent" as a Date/Time Data Type, but you want to store the time it takes to view a video.
You cannot store a duration in a Date/Time field type!


Time is when you START viewing the video.
Duration is the length of time it takes to view the video.

What do you get when you subtract 2 times???
Say you start viewing a video at Noon and you finish at 2PM. How long did it take you to view the video? Do you write it like "2:00" or 2 hours (or 120 minutes)?
Change the start viewing time to 4:30PM and you finish at 6PM. How long did it take you to view the video? Do you write it like "1:30" or 1.5 hours (or 90 minutes)?

If you want to store the duration as 1 field, you must decide if the units should be hours or minutes.
If hours, the data field type could be a number - either Single or Double. Then you could enter 1.5 or 1.75 (hours). But you would not enter 1:50 or 1:75.
If minutes, the field type would be (most likely) Integer. Then you would enter 90 or 120 (minutes).

You could use 2 fields. One field for hours and one field for minutes (both Integer data types). Then you would enter hours (2) and minutes (30).


Personally, I would use 1 field (Integer) to save the duration. If/when you start summing the durations, it is easier (?) to divide by 60 to get hours, rather than to add 1.25 hours and 2.3 hours.



The field "TimeSpent" should be an INTEGER data type.