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.

[MMcCarthy]

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.

Good idea Adrian

Mary

[barkarlo]

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];

I have similar problem.
Endtime sometimes pass midnight and in this case it is less then starttime.
How can I calculate totalhours and grandtotal hours in query

[MMcCarthy]

I have similar problem.
Endtime sometimes pass midnight and in this case it is less then starttime.
How can I calculate totalhours and grandtotal hours in query

   
SELECT Sum(IIf([end time] > [start time], [end time] - [start time],
(#24:00# - [start time]) + [end time]) As TotalHours
FROM TableName;

I think I have the logic of this right. Check it out and let me know.

Mary

[NeoPa]

Not really.
Actually, the difference between two Date/Times is the same regardless of whether or not midnight comes between them or not.
The original code works fine.

[Killer42]

Not really.
Actually, the difference between two Date/Times is the same regardless of whether or not midnight comes between them or not.
The original code works fine.

I can't believe I only just spotted this thread! :) It’s so close to what I have been trying to get right lately.

One question, though - the whole logic of adding [NightTime] seems suspicious to me. Is that another date/time field, or just a number of hours? If the latter, then OK. If the former, then the whole concept seems invalid.

Consider the actual values in some date/time fields. If we take some arbitrary values...

[U]Field		Value			Numeric          _[/U]
StartTime	#1/1/2006 10:00:00 AM#	38718.4166666667
EndTime		#1/1/2006 3:00:00 PM#	38718.625
NightTime	#1/1/2006 10:00:00 PM#	38718.9166666667

EndTime – StartTime + NightTime = 38719.125

Depending on how you want to display it, that’s either 03:00 the following morning, or just under one million hours.