How do I get a query to return summed time fields? or ....

[PeterDuthie]

It'll probably be more clear if I show you the query I'm using:

SELECT DecontaminationRecord.Location, Sum(DecontaminationRecord.LOShrs_mins) AS LOS_SUM, tblLocation.Loc
FROM DecontaminationRecord, tblLocation
WHERE (DecontaminationRecord.[Date] = [Forms]![frmGetDate]![DateForReport]) and (tblLocation.ID = DecontaminationRecord.Location)
GROUP BY DecontaminationRecord.Location, tblLocation.Loc;

The query is supposed to return a record set of the location of a decontamination centre, and the sum of the time spent decontaminating vehicles at the various decontamination centre. (It's to do with Foot and Mouth in the UK in case your interested)

running the query get's me something like this.

Location LOS_SUM Loc
1 1.38888888888888E-02 Yard
4 9.02777777777775E-03 RDX 3
6 5.20833333333333E-02 RDX 7

The problem is the LOS_SUM which is a a time field (it holds a time period - hence LengthOfStay = LOShrs_mns) The query gets data for a report which needs the time periods summed. I can't see how to get the query to return a time nor can I see how to convert the figure returned to a time period in the report.

Please help me again: I've already told people that this report will be easy to do so I'm going to look (deservedly?) foolish if I don't get this done today.

Cheers,
Peter

 

[PeterDuthie]

Hello All. I've found "an" answer: CDate will convert the number to a date. This works OK on the report side but I'm still interested in a solution to the problem at the SQL side - if there is a solution.

Cheers, and thanks for reading this,
Peter

 

[BoxHead]

Peter,

I'm not a member so I can't do a keyword search, but it seems that your question was addressed a couple of weeks ago.

The answer, as I recall, had to do with converting hours to minutes before summing and converting back to hours and minutes for display.

Good luck to you. (I've been known to make promises a bit prematurely myself.)

BoxHead

 

[PeterDuthie]

Thanks Boxhead - I've done a search but can't find (or missed) the thread you mention. If you come across it again I'd be interested to see what solution was put forward.

Cheers,
Peter

 

[CaptainD]

Try using Datediff

UPDATE FIR2_INC SET RESPONSETIME = DATEDIFF("S", [DISPATCHCOMBINED], [ARRIVALCOMBINED])/60;

This snipit calculates the difference down to the second but you can do it by Minute "M" , hour "H" or days "D" and change the "/60" to what you need for the divisor.

 

[Guest_imported]

If your times are formatted as ShortTime then the most accurate way I've found was written by Raskew. First you would create a Select Query and convert the value LOShrs_mins to minutes. Put this in a blank column of your Select query
ConvertedTime[LOShrs_mins]*24)*60
This will convert the value to minutes. Then in the Totals Query you would have this SQL
SELECT Sum(YourSelectQuery.ConvertedTime) AS SumOfConvertedTime, Int([SumOfConvertedTime]/60) & ":" & [SumOfConvertedTime] Mod 60 AS Total
FROM YourSelectQuery;
Every other way converts to decimals and that always leaves values that don't end up neatly.
Paul

 

[PeterDuthie]

Thank very much CaptainD and Paul. I've copied your code to a text file and I'll play around with it when I have bit of time. The CDate() will do for now, but I keep thinking I'm missing simple ways of doing things - or rather, letting VBA do the things for me

Cheers,
Peter