Time calculation: adding and displaying hours greater than 24 2

[michellecole]

Ok, I give.

I've tried to incorporate several Time Calculation Tips and now I've confused myself to the point of not knowing if I'm way off track or almost there. Below is what I want to do and what I have done thus far. Is this the best way?

I am working in Windows 2000, in Microsoft Access. The ultimate goal is to have a report display the following for each OperatorID for a given date range: The total Time spent Processing, the Total Items Processed, and from these two calculations, the total Items Per Hour. Life is good on the Volume and Items Per Hour. And as long as the report's date range is for one day, all is good because an Operator can't work over 24 hours in a day. But once a date range is used that allows a single Operator to have more than 24 hours of processing time, I have an issue with the Time Calculation. For instance, if the Time should be 30 hours, it displays as 6 hours. (I'm assuming there must be a 24 hour cap since 30 - 24 = 6)? Anyway, here is how my time is collected and calculated...

tblTimeLog has the following fields:
OperatorID, Text, 3
TimeType, Text, 8
DateStart, DateTime, format: "mm/dd/yyyy"
TimeStart, DateTime, format: "hh:nn:ss"
DateEnd, DateTime, format: "mm/dd/yyyy"
TimeEnd, TimeStart, DateTime, format: "hh:nn:ss"

An Operator has many records appended into tblTimeLog per day. Here is some sample data (which should yield 15 hours per record):

OperatorID TimeType DateStart TimeStart DateEnd TimeEnd
106 Bulk 12/21/2005 8:59:59 12/21/2005 23:59:59
106 Standard 12/22/2005 8:59:59 12/22/2005 23:59:59
106 Bulk 12/23/2005 8:59:59 12/23/2005 23:59:59
106 Standard 12/24/2005 8:59:59 12/24/2005 23:59:59

When the report is run, different types of records, including Time records, are populated into a work table, wtbl_DocPrepIndividualStats, via queries. Below is the query that populates the Time:

INSERT INTO Wtbl_DocPrepIndividualStats ( OperatorID, [Date], StandardTime, BulkTime, NetworkID, RecordType )
SELECT tblTimeLog.OperatorID, tblTimeLog.DateStart, IIf([BatchType]="Standard",DateDiff("s",[TimeEnd],[TimeStart])/86400,0) AS StandardTime, IIf([BatchType]="Bulk",DateDiff("s",[TimeEnd],[TimeStart])/86400,0) AS BulkTime, NetworkUserName() AS NetworkID, "Time" AS [Time]
FROM tblTimeLog
WHERE (((tblTimeLog.DateStart)>=CDate([Forms]![frmReporting]![txtStartDate]) And (tblTimeLog.DateStart)<=CDate([Forms]![frmReporting]![txtEndDate])) AND ((tblTimeLog.TimeType)="Tray") AND ((tblTimeLog.BatchType)="Standard" Or (tblTimeLog.BatchType)="Bulk") AND ((tblTimeLog.IsRecActive)="Yes"))
ORDER BY tblTimeLog.DateStart;

Below are some of the fields of the work table and what the data looks like after the above query is run on the above sample data for OperatorID 106:

wtbl_DocPrepIndividualStats (Work Table)
OperatorID, Text
StandardTime, Number, Decimal, (Precision 18, Scale 9)
BulkTime, Number, Decimal, (Precision 18, Scale 9)

OperatorID StandardTime BulkTime
106 -0.625 0
106 0 -0.625
106 -0.625 0
106 0 -0.625

The recordsource for the report is a select query against the work table as follows:

SELECT Wtbl_DocPrepIndividualStats.Date, Sum(Wtbl_DocPrepIndividualStats.StandardVolume) AS SumOfStandardVolume, Sum(Wtbl_DocPrepIndividualStats.BulkVolume) AS SumOfBulkVolume, Sum(Wtbl_DocPrepIndividualStats.StandardTime) AS SumOfStandardTime, Sum(Wtbl_DocPrepIndividualStats.BulkTime) AS SumOfBulkTime, Wtbl_DocPrepIndividualStats.OperatorID, Wtbl_DocPrepIndividualStats.OperatorName, Wtbl_DocPrepIndividualStats.JobTitle, Sum(Wtbl_DocPrepIndividualStats.BranchErrors) AS SumOfBranchErrors
FROM Wtbl_DocPrepIndividualStats
WHERE (((Wtbl_DocPrepIndividualStats.NetworkID)=NetworkUserName()))
GROUP BY Wtbl_DocPrepIndividualStats.Date, Wtbl_DocPrepIndividualStats.OperatorID, Wtbl_DocPrepIndividualStats.OperatorName, Wtbl_DocPrepIndividualStats.JobTitle;

I have a set of similar time calculation fields on the report to calculate the Time at the OperatorID Footer, the Job Title Footer and the Report Footer.

Here are the fields at the OperatorID Footer level:

TxtOperIDStandardHrs =Format(Int(DatePart("h",Sum([SumOfStandardTime]))),"00")
TxtOperIDStandardMin =Format(Int(DatePart("n",Sum([SumOfStandardTime]))),"00")
TxtOperIDStandardSec =Format(Int(DatePart("s",Sum([SumOfStandardTime]))),"00")
txtStandardTime_OperID =([txtOperIDStandardHrs]+Int([txtOperIDStandardMin]/60)) & ":" & Format((Int([txtOperIDStandardMin] Mod 60)+Int([txtOperIDStandardSec]/60)),"00") & ":" & Format(Int([txtOperIDStandardSec] Mod 60),"00")

TxtOperIDBulkHrs =Format(Int(DatePart("h",Sum([SumOfBulkTime]))),"00")
TxtOperIDBulkMin =Format(Int(DatePart("n",Sum([SumOfBulkTime]))),"00")
txtOperIDBulkSec =Format(Int(DatePart("s",Sum([SumOfBulkTime]))),"00")
txtBulkTime_OperID =([txtOperIDBulkHrs]+Int([txtOperIDBulkMin]/60)) & ":" & Format((Int([txtOperIDBulkMin] Mod 60)+Int([txtOperIDBulkSec]/60)),"00") & ":" & Format(Int([txtOperIDBulkSec] Mod 60),"00")

TxtOperIDHrs =Format((Int(DatePart("h",Sum([SumOfStandardTime]))))+(Int(DatePart("h",Sum([SumOfBulkTime])))),"0000")
TxtOperIDMin =Format(Int(DatePart("n",Sum([SumOfStandardTime])))+(Int(DatePart("n",Sum([SumOfBulkTime])))),"00")
txtOperIDSec =(Int(DatePart("s",Sum([SumOfStandardTime]))))+(Int(DatePart("s",Sum([SumOfBulkTime]))))
txtTime_OperID =([txtOperIDHrs]+Int([txtOperIDMin]/60)) & ":" & Format((Int([txtOperIDMin] Mod 60)+Int([txtOperIDSec]/60)),"00") & ":" & Format(Int([txtOperIDSec] Mod 60),"00")

The only (3) Time fields that are visible on the report at the OperatorID Footer level are txtStandardTime_OperID, txtBulkTime_OperID and txtTime_OperID, and for the above OperatorID's data is currently displaying as: 6:00:00, 6:00:00, 12:00:00. But the data should have reflected something like 30:00:00, 30:00:00, 60:00:00. And in cases where the user might enter a date range for a full year, I would need something to accommodate 2000+ hours, 2080:00:00??

Where did I take the wrong turn? What is the best way to accomplish the time calculations?

Thank you,
Michelle

 

[dhookom]

Without reading all of your email (I'm lazy and time challenged), I think you are attempting to use a date/time which is a point in time as a duration of time.

Consider displaying time durations as a number of hours or minutes. Working from 1:00 PM to 5:30 PM should be 4.5 hours, not 4:30.

Doug Steele has a great function at

http://www.accessmvp.com/djsteele/Diff2Dates.html

which can display your duration as if it was a point in time.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[michellecole]

I am attempting to calculate many time durations and then sum them. The durations can be 4 seconds to 2080 hours.

Cool Function, and if I understand it correctly it determines the difference between (2) dates and displays the results. I am needing to determine the difference between (2) dates (for many records) but then I also need all of those results summed up.

For instance, utilizing the Date2Diff function in a query I get the following results on (3) records:

6 minutes 59 seconds
4 seconds
2085 hours

But now how do I add these (3) records to display:
2085 hours 7 minutes 3 seconds.

Thank you,
Michelle

 

[dhookom]

Assuming you have two fields [Start] and [End] that you wanted to display a sum in a group or report footer. You would use a text box with a control source like:
[tt][blue]
=Diff2Dates("hns",0,Sum([End]-[Start]))
[/blue][/tt]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[michellecole]

Beautiful!

Thank you!