how to calculate elapse time using a query

[ivyjoe]

I have a AS400 database table that stores time in the following manner 70535 (hour, minutes, seconds). Utilizing access 2010, I have used the left, mid, and right command to separate hour, minute, and second into their are separate table values 7 05 35. The AS400 database stores 3:15:30pm in the following manner 151530.

At this point, I used the string function and created the following table value 7:05:35.

Overall, I am needing to convert 70535 to time and then calculate lapse time where the employee name is the same. my database has about 2500 unique employee records per day. I am utilizing a query to perform the above.

date name tstime hour minutes second time
2/8/14 NJohn 151530 15 15 30 15:15:30

 

[plog]

So what is your question? You've provided 1 record with just one time example; to calculate lapsed time a second time is needed.

Provide some sample data from your table (include field and table named). And then show what data should be produced as a result of that sample data.

 

[ivyjoe]

Thanks for the feedback. I have attached a file that depicts both the detailed records and a summary tab of how I would like have the final data output. The table names are on top of each tab on the attached .xls file.

Overall, I am looking for a summary by date, by person, by transaction type, with count of tspn and the total lapsed time (see summary tab).

 

[Monardo]

Hi

You need to make Group By query. This is the SQL of the query.

SELECT 
  Pull_packed.Date
, Pull_packed.[Transaction Type]
, Pull_packed.Name
, Count(Pull_packed.TSPN) AS CountofTSPN
, Sum(CDate([Pull_packed].[Time])) AS [Total Elasped time]

FROM Pull_packed
GROUP BY Pull_packed.Date, Pull_packed.[Transaction Type], Pull_packed.Name;

 

[ivyjoe]

I want to first thank you for the great help. It feels like we are very close, then only issue is the lapsed time calculation.

The attached .xls file has a tab labeled "detailed output" (see col. K for a manual calculation that I added -- row's 503-506),

and the tab labeled "new sql query output" (see col. G for the manual calculations that I believe are correct numbers -- row's 594-595). Thanks again for the great help...


P.S. I would also like to convert tstime to time in fewer steps if possible (see attached .xls for details).

 

[namliam]

You can do that convertion easily either in a function or in a single line:

Public Function convSTime(TimeNumber As Double) As Date
    Dim tempTime As String
    tempTime = Format(TimeNumber, "00:00:00")
    convSTime = CDate(tempTime)
End Function

or in-line in the query:

Cdate(format(TSTime, "00:00:00"))

In your sample you probably have a typo, your elapsed time says 2:45:33 ...
It actually is 2:35:33

Date and time values belong together you may consider joining the two to a single field....
Assuming you want the total time per " name " which btw is a reserved word much like " date " and " time ", dont use reserved words or you will run into problems

Perhaps you want some query like:

Select YourDate, YourName, min(YourTime), Max(yourTime), Max(yourtime) - Min(yourtime) as Difference
From yourtable
group by yourdate, yourname

YourTime in this case I am assuming to have already been converted to proper time values with one of the above 2 options