Sum time in query 1

[bradley1]

Question,

I have a Access2003 table that has a field w/a daytatype of "date/Time" w/format of "Short time". I'm trying to sum each activities time listed below (by each Activity) in a basic query for a Access form:

Activity SumofHours

Adjustments 0:37
Adjustments 0:45
Adjustments 1:00
Appeals 3:45
DataEntry 1:25
DataEntry 3:00
Dashboards 0:10
Dashboards 0:33

Thanks in advance

 

[PHV]

Something like this ?
SELECT Activity, Sum([your Date/time field])
FROM yourTable
GROUP BY Activity

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bradley1]

PHV--

I tried your query and unfortunately it did the same thing it did to me. Here was the outcome:

fldClaimType SumOffldNonEarned

Adjustments 2.22291666666667
Appeals 0.15625
ASK 0.398611111111111
Dashboards 0.186111111111111
dataentry 0.165972222222222
Participation 0.147916666666667

Thoughts...

 

[PHV]

SELECT fldClaimTyp, (24*Int(Sum(fldNonEarned))+Format(Sum(fldNonEarned),'h')) & Format(Sum(fldNonEarned),':nn:ss') AS SumOffldNonEarned
FROM yourTable
GROUP BY fldClaimTyp


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

IMHO, you shouldn't be using a date/time field to store a duration of time. I would use a numeric field to store the minutes (or other duration). I use date/time fields to store a point in time.

Duane
Hook'D on Access
MS Access MVP

 

[bradley1]

PHV...Thanks so much for your response. The revised code worked-- I just had to remove the "SS" part because I didn't need the seconds. Again thanks so much and enjoy a well deserved star!!

 

[bradley1]

PHV,

Sorry to bother you again but I have one more issue. Now that my summing time query works for a access form--how do I sum all of those hours together at the bottom of a form? Here are the results from the first step/query:

Activity SumOfNonEarned
Adjustments 68:36
Appeals 3:45
ASK 12:08
Dashboards 2:43
Left Early 0:10
Other 2:36
Participation 3:33
QAS Time 19:27