Lost in Time (conversion)

[Guest]

Very new to Access and all that goes with it. Please be gentle.

Have created a database that has several tables, queries and reports. One of
the tables has a field (duration) that shows in seconds on how long a user
accesses my system.

Created a query that groups the user and sums the duration field. Works great.

Now I would like to convert the summed duration field, that shows total
seconds into the hh:nn:ss format. Then to show only users with more than 300
seconds of use. Then to sort by total duration in decending order.

Should this be done in the query? Or a report?

A copy of the SQL statement from the Query follows.

SELECT DISTINCTROW [TalkGroups].[Talk_Group_Name],
Sum([SystemFiles].[duration]) AS [Sum Of duration]
FROM TalkGroups INNER JOIN SystemFiles ON
[TalkGroups].[ID_Number]=[SystemFiles].[group 1]
GROUP BY [TalkGroups].[Talk_Group_Name]
HAVING (((Sum(SystemFiles.duration))>300))
ORDER BY Sum([SystemFiles].[duration]) DESC;

Any help would be appreciated.

Wes

I reserve the right to ask 1000's of other newbie questions.

 

[Larry Linson]

I'd use a criteria line on the total seconds field to choose only those over
300 seconds.

Access does not have a date/time format specifically for showing an amount
of time... the Date/Time type Field or Variable is for showing a point in
time... a date and time. So, if you want to show the seconds as you
describe, you'll need to calculate the hours, minutes, and seconds
separately, and show each as a number with labels for the : separator. You
can probably find a link to a fuction if you Google, but if the number of
seconds is over 3600, divide by 3600 to get hours... subtract the number of
hours times 3600 from the original to get minutes and seconds, divide by 60
to get minutes, subtract minutes times 60 from the calculated minutes and
seconds to get seconds.

You can keep the time in a Date/Time field, but be aware that it will
represent that time on Dec. 30, 1899, so Date/Time calculations may not be
exactly as you'd expect.

Larry Linson
Microsoft Access MVP