CODEWITHSUNDEEP
sqlms-access
Tantoon
Tantoon

Reputation: 47

MS ACCESS how to average 3 columns in 2 tables

I have the following tables:

T1:
Repair_ID, Descreption,Cost,Level
(1,a,24,9)
(2,b,34,9)
(3,a,22,3)
(4,c,11,6)

T2:
Repair_ID, Start_Time, End_Time,Location_ID
(1,02:00:00,03:00:00,3)
(2,04:00:00,05:00:00,7)
(3,06:00:00,08:00:00,3)

I want to be able to get a table that contains an average of the cost, an average of the level, and an average of the duration of the repair(by subtracting end_Time from start_time) grouping by Descreption and Location_ID so the table would look like this: Descreption, Location_ID, AvgCost,AvgLevel, Avg Duration (a,3,(24+22/2)=24,(9+3/2=6), 01:30:00*)

Upvotes: 0

Views: 736

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52645

This actually pretty straight forward. Join the three tables and do the calculations and grouping you've described

Update Formatting an average of Difference of dates is a little tricky. You need to do the format after the Aggregate has been performed. The easiest way to do this is to use an in line view

SELECT 
     Descreption,
     Location_id,
     AvgLevel,
     AvgCost,
     Format(CDATE(AvgDuration),"hh:mm:ss") AvgDuration
FROM
(
    SELECT
        repair.Descreption,
        location.Location_id,
        AVG(repair.level) AvgLevel,
        AVG(repair.Cost) AvgCost,
        AVG(times.end_time - times.start_time)  AvgDuration 

    FROM 
        T3 location
        INNER JOIN t1 repair
        on location.repair_id = repair.repair_id
        INNER JOIN t2 times
        ON location.repair_id = times.repair_id
    GROUP BY
        repair.Descreption,
        location.Location_id) foo
ORDER BY
       AvgCost desc

Upvotes: 2

Related Questions