query sun of time fields

[kds14589]

I'm new to acess and working on my first db. I haven't started using code yet so i still working with wizards. I am using Acess 2000 on an Windows XP.
My problem is i have a time field that i need too run a query on and the sum needs to be in hundreds of hours but i've tried everything and as soon as the sum gets to 23:59 it starts over at 0:00. I need this to show large hours numbers

[kds14589]

oooooooooookkkk kkkkkkkkkkkk

[MMcCarthy]

I'm new to acess and working on my first db. I haven't started using code yet so i still working with wizards. I am using Acess 2000 on an Windows XP.
My problem is i have a time field that i need too run a query on and the sum needs to be in hundreds of hours but i've tried everything and as soon as the sum gets to 23:59 it starts over at 0:00. I need this to show large hours numbers

Try this:

 
SELECT Field1, Field2, 
CInt(Sum([TimeField])) * 24 +  Hour(Sum([TimeField]) As TotalHours,
Minute(Sum([TimeField])) As Mins
FROM Tablename
GROUP BY Field1, Field2;
 
 

[NeoPa]

Or a slightly modified version :

SELECT Field1, Field2, 
    Int(Sum([TimeField])) * 24 + Hour(Sum([TimeField]) As TotalHours, 
    Minute(Sum([TimeField])) As Mins
FROM Tablename
GROUP BY Field1, Field2

Not just for fun - CInt rounds as it goes. Int() truncates, as is required here.

[MMcCarthy]

Or a slightly modified version :

SELECT Field1, Field2, 
Int(Sum([TimeField])) * 24 + Hour(Sum([TimeField]) As TotalHours, 
Minute(Sum([TimeField])) As Mins
FROM Tablename
GROUP BY Field1, Field2

Not just for fun - CInt rounds as it goes. Int() truncates, as is required here.

Believe it or not, I originally had it as an Int but changed my approach a couple of times and ended up with CInt. I never remember the rules anyway. Have to go look them up on a regular basis. My mind is like a sieve.

Mary

[kds14589]

Thanks Neopa and mmccarthy
I’m still having trouble with this one. I never used code before so be patient. After this database is finished this week I’ll learn more about code.
I have a table called [PTP Vehical daily log] (don’t mind the misspelling)
I have three fields in it [start time], [end time], and [night time].
I need to subtract the [start time] from the [end time] then add the [night time] to this total.
Finally I need a grand total in hours that will not roll over at 23:59 like it does.
I tried to follow your example but I keep getting a ‘snytax error (missing operative)’.Wha t I came up with is below.
I would appreciate any help you can give and I promise if I can get this working I wont bother you anymore!!!!!!!! !!!


SELECT
CInt(Sum(([PTP Vehical daily log]![end time]-[PTP Vehical daily log]![start time])+[PTP Vehical daily log]![night time]) * 24 + Hour(Sum(([PTP Vehical daily log]![end time]-[PTP Vehical daily log]![start time])+[PTP Vehical daily log]![night time])) AS TotalHours, Minute(Sum(([PTP Vehical daily log]![end time]-[PTP Vehical daily log]![start time])+[PTP Vehical daily log]![night time])) AS Mins
FROM [PTP Vehical daily log]
GROUP BY [PTP Vehical daily log].[start time], [PTP Vehical daily log].[end time], [PTP Vehical daily log].[night time]

[MMcCarthy]

OK try this, the group by is only needed if you want your results broken out over something like EmployeeID. I think the main problem was a misplaced bracket.

 
SELECT (Int(Sum(([end time] - [start time]) + [night time])) * 24 
+ Hour(Sum(([end time] - [start time]) + [night time])) AS TotalHours, 
Minute(Sum(([end time] - [start time]) + [night time])) AS Mins
FROM [PTP Vehical daily log];

[kds14589]

It Worked

Thanks

[MMcCarthy]

It Worked

Thanks

No Problem

[NeoPa]

SELECT Sum([end time] - [start time] + [night time]) AS TotTime, _
    Int(TotTime) * 24 + Hour([TotTime]) AS TotalHours, _
    Minute([TotTime]) AS Mins
FROM [PTP Vehical daily log];

Bear in mind, unless you want this across the whole recordset, you'll need a GROUP BY clause.
This is fundamentally the same as Mary's code.
The advantage is the complicated field is worked out just the once (in the code - Mary's would also be worked out once at execution) and then reused more ligibly. It also introduces a useful concept available in SQL.
I hope this makes sense.