Summing Time Fields 2

[diggerbob]

I have made a query that has the following fields:
Date
System1StartTime
System1StopTime
System1RunTime (this field I formulated as follows, [System1StopTime]-[System1StartTime])
I use the "short time" format. I get the correct figure when I run the query.
Now for my problem. When I created my report I needed to sum System1RunTime to get a total of hours that system1 ran for the month and also an average run time for the month but both controls do not come out right. Can anyone help in this matter?

 

[SkipVought]

diggerbob,

but both controls do not come out right

What is not right?

Please post an example including Start, Stop & Run times.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[diggerbob]

Skip,
This is only a partial sample of what is in my database. I did not include the date field. Does this help? Can the system run time be returned in another format that could be calculated more easier?



Sys#1 Start Time Sys#1 Stop Time Sys#1 Run Time

6:06 11:26 5:20
6:05 12:00 5:55
6:00 12:15 6:15
6:00 10:00 4:00
5:40 12:05 6:25
5:55 11:14 5:19

thanks,
Bob

 

[diggerbob]

Skip,
I forgot to send you the formulas in my controls on my report. The one control was =Sum([System1RunTime]), the other one was this for an average of =Avg([System1StopTime]-[System1StartTime]). They do not calculate correctly.
thanks
bob

 

[SkipVought]

Your time values are in FRACTIONS OF A DAY.

Will you EVER have more than 24 hours of RUN TIME in any ROW?

Assuming NOT...

1) Sum your Run Times: TRun = SumOfRunTimes

2) The HOURS, MINUTES, SECONDS of Total Run Time...
[tt]
THrs = Int(TRun*24)
TMin = Int((TRun - THrs/24)*24*60)
TSec = Int((TRun - THrs/24 - TMin/24/60)*24*60*60)
[/tt]



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[diggerbob]

Skip,
No, my run times will never go over 24 hours in any given row. Only when I want to see the total for the month. Your help is much appreciated. I will try this tomorrow to see how it works.
Thanks,
Bob

 

[PHV]

And what about something like this ?
Int(Sum([System1RunTime]))*24+Hour(Sum([System1RunTime])) & ":" & Minute(Sum([System1RunTime]))
With the data posted, the result is 33:14
And this ?
Format(Avg([System1StopTime]-[System1StartTime]), "hh:mm")
Result is 05:32

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[diggerbob]

Thanks for all the help Skip and PH. The solution works great! I am slightly more than a beginner and am learning on my own. Hopefully when the company hires me on as a permanent employee, I want to take some Access Classes. I like working in it but it can be a little mind boggling.
Again,
Thanks,
Bob

 

[chazgaz]

I am having trouble with this solution. I am very new to access and I am trying to accomplish this exact same thing. I have a table that includes a start time and an end time. I have built a query that calculates the elapsed time.

What / Where do I put this formula so I can sum up my daily elapsed time.

Do I have to create a separate query for this? (When I try creating a separate query I get a Data type mismatch in criteria expression error)

If I run this formula in the query that calculates the elapsed time I get the following message...You tried to execute a query that does not include the specified expression 'ScanDate' as part of the aggregate function. (ScanDate is one of the fields in my table, if I remove that field from the query, I get the same message for other fields that are part of the query)

Hopefully this makes some sort of sense.

I appreciate any assistance.

Thank you

 

[lespaul]

Both of PHV's solutions can be entered directly in the query design grid. The "You tried to execute ..." error occurs when you have a calculated expression using an aggregate function (like SUM or AVG) and you don't include the non aggregate fields in a group by clause.

So if my query is:

SELECT FIELD1, SUM(FIELD2) FROM TABLENAME

I get the "you tried to executed" error

however, if you add all the non aggregage fields to the group by clause, you eliminate the error:

SELECT FIELD1, SUM(FIELD2) FROM TABLENAME GROUP BY FIELD1

if I then find that I need FIELD3 in the query:

SELECT FIELD1, FEILD3, SUM(FIELD2) FROM TABLENAME GROUP BY FIELD1, FEILD3



Leslie

 

[diggerbob]

chazgaz,
I believe I put this formula in a control on my report rather than in the query but I will double check on Monday when I return to work and let you know for sure.
Thanks for your help also Leslie.
Bob

 

[chazgaz]

Thank you everyone for your help but I am still stuck. Hopefully you all don't get sick of my stupidity.

Here is what my SQL view looks like...

SELECT dbo_LogTable.ScanDate, Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)) AS [Elapsed Time], Int(Sum([Elapsed Time]))*24+Hour(Sum([Elapsed Time])) & ":" & Minute(Sum([Elapsed Time])) AS Expr1
FROM dbo_LogTable
GROUP BY dbo_LogTable.ScanDate, Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded));

If I take out the group by statement, I get the aggregate function error...If I add the group by statement I get the data type mismatch error.

For simplicity I have taken a couple of fields out of my query such as employee name and employee type. The only thing I am trying to group by currently is scan date.

Please let me know if you need any additional information.

 

[lespaul]

SELECT dbo_LogTable.ScanDate, Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)) AS [Elapsed Time], Int(Sum(Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded))))*24+Hour(Sum(Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)))) & ":" & Minute(Sum(Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)))) AS Expr1
FROM dbo_LogTable
GROUP BY dbo_LogTable.ScanDate

I don't think you can use the alias within the same query. Does this query work for you correctly?

Leslie

 

[chazgaz]

I get a message that states that I did not include the specified expression Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded))as part of an aggregate function.

Thank you again...I am also trying to get this to work as a control on a report.

 

[lespaul]

so what happens if you add that to the group by:

SELECT dbo_LogTable.ScanDate, Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)) AS [Elapsed Time], Int(Sum(Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded))))*24+Hour(Sum(Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)))) & ":" & Minute(Sum(Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)))) AS Expr1
FROM dbo_LogTable
GROUP BY dbo_LogTable.ScanDate, Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)), Int(Sum(Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded))))*24+Hour(Sum(Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)))) & ":" & Minute(Sum(Int(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded)/60) & ":" & Format(DateDiff("n",dbo_LogTable!TimeStarted,dbo_LogTable!TimeEnded))))

Leslie

 

[chazgaz]

This option states that I cannot have an aggregate function in the GROUP BY clause

 

[PHV]

And what about this ?
SELECT A.ScanDate, Int(Sum(A.[Elapsed Time]))*24+Hour(Sum(A.[Elapsed Time])) & ":" & Minute(Sum(A.[Elapsed Time])) AS TotalTime
FROM (SELECT ScanDate, Int(DateDiff("n",TimeStarted,TimeEnded)/60) & ":" & Format(DateDiff("n",TimeStarted,TimeEnded)) AS [Elapsed Time]
FROM dbo_LogTable) A
GROUP BY A.ScanDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[chazgaz]

I still get the data type mismatch in criteria expression error

 

[PHV]

type mismatch in criteria expression error
???
NO criteria in my SQL statement !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244