Hours calculation problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following query that is supposed to show total hours but the hours
show up as 09:187 in the Total Hours: [THrs] & ":" & [TMin] part of the query.

How can I fix it?

Thank you.
 
Sorry I forgot to post the SQL statement...

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, Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin, [THrs] & ":" &
[TMin] AS [Total Hours]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;
 
I suspect the problem is in the

Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin

clause. This will probalby be giving you [TSeconds] Mod 60. Try:

Format(Sum(([TSeconds] Mod 3600)\60),"00") AS TMin

HTH,

Rob

Billy B said:
Sorry I forgot to post the SQL statement...

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, Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin, [THrs] & ":" &
[TMin] AS [Total Hours]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;




Billy B said:
I have the following query that is supposed to show total hours but the
hours
show up as 09:187 in the Total Hours: [THrs] & ":" & [TMin] part of the
query.

How can I fix it?

Thank you.
Click to expand...
Click to expand...
 
Rob,
I tried it and it still did not work. My db contains a field with seconds
only and I would like that converted to HH:MM:SS as the result.

Rob Parker said:
I suspect the problem is in the

Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin

clause. This will probalby be giving you [TSeconds] Mod 60. Try:

Format(Sum(([TSeconds] Mod 3600)\60),"00") AS TMin

HTH,

Rob

Billy B said:
Sorry I forgot to post the SQL statement...

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, Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin, [THrs] & ":" &
[TMin] AS [Total Hours]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;




Billy B said:
I have the following query that is supposed to show total hours but the
hours
show up as 09:187 in the Total Hours: [THrs] & ":" & [TMin] part of the
query.

How can I fix it?

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
First Sum all the seconds
Then divide by 60 to get the number of minutes
Then use the Mod operator to get the number of minutes from 0 to 59 and
remove the hours
Then apply the formatting
1) Sum(TSeconds)
2) Sum(TSeconds) / 60
3) (Sum(TSeconds) / 60) Mod 60
4) Format((Sum(TSeconds) / 60) Mod 60,"00")

Format((Sum(TSeconds) / 60) MOD 60,"00") as TMin

This should round the minutes to the nearest minute

If you wanted to get whole minutes, then change to integer division

Format((Sum(TSeconds) \ 60) MOD 60,"00") as TMin

Rob Parker said:
I suspect the problem is in the

Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin

clause. This will probalby be giving you [TSeconds] Mod 60. Try:

Format(Sum(([TSeconds] Mod 3600)\60),"00") AS TMin

HTH,

Rob

Billy B said:
Sorry I forgot to post the SQL statement...

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, Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin, [THrs] & ":" &
[TMin] AS [Total Hours]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;




Billy B said:
I have the following query that is supposed to show total hours but the
hours
show up as 09:187 in the Total Hours: [THrs] & ":" & [TMin] part of the
query.

How can I fix it?

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top