Calculation question

[Guest]

I am trying to group time information by year. I have a field containting
seconds and have figured out how to show hours and minutes in the query but
cannot figure out how to get the seconds calculated. After getting that
calculation I can concantenate the fields and add the necessary characters.
Below is the sql query in Access 2000.
Thank you.

SELECT DISTINCTROW Year(Statistics.StatDate) AS [Year],
Sum(Statistics.Mileage) AS [Timed Miles], Sum(Statistics.[Total Miles]) AS
[Total Miles], Sum(Statistics.ESeconds) AS SumOfESeconds,
Sum(Statistics.TSeconds) AS SumOfTSeconds, Format(Sum([TSeconds]\3600),"00")
AS THrs, [THrs] & ":" & [TMin] AS [Total Hours], Format(Sum([TSeconds] Mod
3600\60),"00") AS TMin, Sum(Statistics.Elevation) AS [Total Elevation]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;

 

[Marshall Barton]

I am trying to group time information by year. I have a field containting
seconds and have figured out how to show hours and minutes in the query but
cannot figure out how to get the seconds calculated. After getting that
calculation I can concantenate the fields and add the necessary characters.
Below is the sql query in Access 2000.
Thank you.

SELECT DISTINCTROW Year(Statistics.StatDate) AS [Year],
Sum(Statistics.Mileage) AS [Timed Miles], Sum(Statistics.[Total Miles]) AS
[Total Miles], Sum(Statistics.ESeconds) AS SumOfESeconds,
Sum(Statistics.TSeconds) AS SumOfTSeconds, Format(Sum([TSeconds]\3600),"00")
AS THrs, [THrs] & ":" & [TMin] AS [Total Hours], Format(Sum([TSeconds] Mod
3600\60),"00") AS TMin, Sum(Statistics.Elevation) AS [Total Elevation]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;

I think all you want is:
Format(Sum([TSeconds] Mod 60),"00") AS TSec

 

[Guest]

Thank you. That works. But not that I look at the results, I see that the
data in the minutes rows show up as 259 minutes, for example. What I want to
do is with the TSeconds (the sum of all seconds) display hh.nn.ss as
120:45:04 (as an example). What do I need to make my display correct?

I am trying to group time information by year. I have a field containting
seconds and have figured out how to show hours and minutes in the query but
cannot figure out how to get the seconds calculated. After getting that
calculation I can concantenate the fields and add the necessary characters.
Below is the sql query in Access 2000.
Thank you.

SELECT DISTINCTROW Year(Statistics.StatDate) AS [Year],
Sum(Statistics.Mileage) AS [Timed Miles], Sum(Statistics.[Total Miles]) AS
[Total Miles], Sum(Statistics.ESeconds) AS SumOfESeconds,
Sum(Statistics.TSeconds) AS SumOfTSeconds, Format(Sum([TSeconds]\3600),"00")
AS THrs, [THrs] & ":" & [TMin] AS [Total Hours], Format(Sum([TSeconds] Mod
3600\60),"00") AS TMin, Sum(Statistics.Elevation) AS [Total Elevation]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;

I think all you want is:
Format(Sum([TSeconds] Mod 60),"00") AS TSec

 

[John Vinson]

Thank you. That works. But not that I look at the results, I see that the
data in the minutes rows show up as 259 minutes, for example. What I want to
do is with the TSeconds (the sum of all seconds) display hh.nn.ss as
120:45:04 (as an example). What do I need to make my display correct?

Try:

Format(Sum([TSeconds]\3600),"#") & "." & Format(Sum([TSeconds]) \ 60
MOD 60, "00") & "." & Format(Sum([TSeconds] MOD 60, "00")

John W. Vinson[MVP]

 

[Guest]

John
I put this in my query and got the message:
Function has wrong number of arguments.

Thank you. That works. But not that I look at the results, I see that the
data in the minutes rows show up as 259 minutes, for example. What I want to
do is with the TSeconds (the sum of all seconds) display hh.nn.ss as
120:45:04 (as an example). What do I need to make my display correct?

Try:

Format(Sum([TSeconds]\3600),"#") & "." & Format(Sum([TSeconds]) \ 60
MOD 60, "00") & "." & Format(Sum([TSeconds] MOD 60, "00")

John W. Vinson[MVP]

 

[John Vinson]

John
I put this in my query and got the message:
Function has wrong number of arguments.

hrm... misplaced parenthesis I guess: sorry! Try

Format(Sum([TSeconds])\3600,"#") & "." & Format(Sum([TSeconds]) \ 60
MOD 60, "00") & "." & Format(Sum([TSeconds]) MOD 60, "00")


John W. Vinson[MVP]