Advise/Direction on text to hh:nn:ss Calculation 1

[kentwoodjean]

Have query where source table is the result of imported text file. "Duration" column is formated as text (14:22:02)and represents Hours, min & sec. In order to convert this to a number that can be used in calculation for reports, used the hour, minute, and second function in my query.

I want to create a report that will show the combined total per date in hrs, min, sec for each activity listed. Trying to determine the best way to do this. Do I combine the 3 columns into 1 and format using hh:nn:ss and if so, will 60 sec roll to mins, 60 min to hours, and will the hours continue to add beyond 24? If this will work, I do not know how to combine the 3 columns, can do 2 but not 3. If anyone has a better idea on this, please let me know.

Here is my SQL for the query:

SELECT [IWR Mo].ToDate, [IWR Mo].Team, [IWR Mo].Reason, [IWR Mo].TimesUsed, [IWR Mo].Duration, Minute([Duration]) AS [Min], Hour([Duration]) AS Hr, Second([Duration]) AS Sec
FROM [IWR Mo]
GROUP BY [IWR Mo].ToDate, [IWR Mo].Team, [IWR Mo].Reason, [IWR Mo].TimesUsed, [IWR Mo].Duration;

 

[alvechurchdata]

You might like to look at the algorithm presented by CrewChief6 in thread184-1055034. It's in Visual Fox but it doesn't use any advanced functions so conversion to VB is straightforward.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[PHV]

Something like this ?
SELECT ToDate, Team, Reason, TimesUsed
, (24*Int(Sum(Duration))+Format(Sum(Duration),'h')) & Format(Sum(Duration),':nn:ss') AS TotalTime
FROM [IWR Mo]
GROUP BY ToDate, Team, Reason, TimesUsed;

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

 

[kentwoodjean]

PH, tried your solution first. If I understood you correctly and corrected formated everything, I am getting an error for "data mismatch in criteria expression". Here is what my SQL looks like now. Please tell me if I goofed somewhere.

SELECT [IWR Mo].ToDate, [IWR Mo].Team, [IWR Mo].Reason, [IWR Mo].TimesUsed
, (24*Int(Sum(Duration))+Format(Sum(Duration),'h')) & Format(Sum(Duration),':nn:ss') AS TotalTime
FROM [IWR Mo]
GROUP BY [IWR Mo].ToDate, [IWR Mo].Team, [IWR Mo].Reason, [IWR Mo].TimesUsed;

 

[PHV]

in criteria expression
No WHERE nor HAVING clause (ie NO criteria at all) ???

The code I suggested you is the whole SELECT instruction to paste in the SQL pane of the query window.

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

 

[kentwoodjean]

I did as you indicated and pasted the whole SELECT instruction in the SQL pane and still get the same error.I am still rather new at expressions, codes etc, and sometimes the tek talk can be a bit over my head, but could this be because the "Duration" field must first be coverted from its text format to the number format for hrs, min, sec? This is what I did in my first query which left me with the 3 separate columns that I would assume need to be joined together. Once they are joined again like (14:11:01) and are no longer next files would we then write a second query with your SQL.

 

[PHV]

And this ?
SELECT ToDate, Team, Reason, TimesUsed
, (24*Int(Sum(TimeValue(Duration)))+Format(Sum(TimeValue(Duration)),'h')) & Format(Sum(TimeValue(Duration)),':nn:ss') AS TotalTime
FROM [IWR Mo]
GROUP BY ToDate, Team, Reason, TimesUsed;

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

 

[kentwoodjean]

I'm sorry that I did not get back with you sooner but I did want to let you know that I did get it working the way I want. Thanks for your guidance, it really helped.