Whats up with aggregate-functions

[sigi7]

Hi everyone,

I have problems using a SUM aggregate over short-time values hh:mm

For example:
Select Sum(Mileage) as Field1, Sum(TotalLabourCosts) AS Field2, Sum(VanRental) AS Field3, Sum(preparingJob) AS Field4, Sum(doingTheJob) AS Field5, Sum(packingAway) AS Field6

FROM tblInstallCosts WHERE date > " & strFrom & " AND date < " & strTo

the time is in a hh:mmss) format. The SUM agregate wouldnt work over a time field,...why is that and what's the way round this?

somebody please help, cheers fellas.
Sigi from slovakia

 

[Rose412]

hh:nn:ss can't display time greater than 24 hours.

Jon K posted a simple way in the Sample Databases forum to display aggregate time between two date/time fields in the ? day ? hr ? min ? sec format:-
http://www.access-programmers.co.uk/forums/showthread.php?t=62733


The attached database uses his format. The database contains a table "tblData" with these records:-

TotalTime
 23:10:45
  5:12:00
  1:07:36
  1:07:36
  3:00:00

and a query:-
SELECT Int(Sum([TotalTime])) & " day " & Format(Sum([TotalTime]),"h \h\r n \m\i\n s \s\e\c") AS SumTotalTime
FROM tblData;

When the query is run, it will produce:-
SumTotalTime
1 day 9 hr 37 min 57 sec

Hope this helps.
Rose

 

[sigi7]

thanks for the reply! but the piece of code didnt work. I just want to count the hours ignoring days so i.e. 24hours + 3hours would give 27hours.

and if some fields in the database table are text fields, but they store numeris values, can access perform aggregate AVG/SUM function on them anyway.

cheers

 

[Rose412]

Jon K's sample includes also the expression for displaying in hours, minutes and seconds.


I am using Access 2000. On my system, I can use Sum() and Avg() on text fields as if they were numeric fields.

If you can't do it on your system, you can use one of the conversion functions to convert a text field to numbers before using Sun() or Avg(), e.g.

Select Sum(CInt([TextFieldName])) as SumOfNumbers


CInt() will convert the text values to integer numbers.

 

[The_Doc_Man]

The question is whether the fields in question are date/time fields or text fields.

If you are going to use hh:mm:ss (or similar) text fields as inputs, my best advice is to write yourself two functions.

One takes a string of the "hh:mm:ss" format as input and returns a number of seconds or minutes. (Your choice, but be consistent.) The other takes a number of seconds or minutes and returns a string of the form "hhhh:mm:ss" or "ddd-hh:mm:ss" as you choose. (Heck, one of each wouldn't hurt, either.)

If you make these two functions public and store them in a general module, you can use them anywhere - queries, forms, reports. The text-in, integer-out function can use InStr and Mid functions to separate the fields and the Val function to evaluate each field. The integer-in, text-out function can do sexagesimal math (successive division by 60 and watch the remainders carefully) plus a Format function (with leading zeros appropriately addressed) to build the individual hours, minutes, and seconds.