CODEWITHSUNDEEP
sqlms-access
Tantoon
Tantoon

Reputation: 47

MS Access average after subtracting

how can I subtract these two columns and then find their average grouping by col3:

col1,     col2,     col3
02:00:00, 04:00:00, 4
02:00:00, 05:00:00, 3

i want the result in this format: Hours:Minutes:Seconds

Thank you

Upvotes: 0

Views: 347

Answers (2)

BIBD
BIBD

Reputation: 15384

Get the difference of col1 and col2 in seconds

select col3, datediff("s", col2, col1) as diff_n_seconds from mytable

and save that as a query

Then build a group-by query on the first query. Put an average on it

select avg(diff_n_seconds), col3 from myquery groupby col3

and convert the avg back to a time format using the format function - e.g.,

Format(int([FieldName]/3600),"00") & ":" & Format(Int(([FieldName]-(Int([FieldName]/3600)*3600))/60),"00") & ":" & Format((([FieldName] Mod 60)),"00")

Reply With Quote

(you could do this in the same query, or build a new one off of the previous one)

Upvotes: 1

mwolfe02
mwolfe02

Reputation: 24227

SELECT col3, Format(Avg([col2]-[col1]),"hh:mm:ss") AS TimeDiff
FROM Table1
GROUP BY col3;

Upvotes: 1

Related Questions