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 fro开发者_开发百科m 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*)

• 1 hour for repair_ID 1 and 2 hours for repair_ID 3 = 01:30:00 on average since these two repairs happened in the same location and have the same descreption thank you

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