Following is best I can accomplish with this data as I am still not clear how the OIA data should be resolved. I fixed the kmINI and kmEND variances. The results are still not what you say should be. How did you derive your numbers?
Query1: DISTINCTRoadsOIA
SELECT DISTINCT tblTasks.RoadDesignation, tblTasks.OIA, tblTasks.InspectionExtension FROM tblTasks;
Query2:
SELECT tblRoutes.ROUTE, tblRoutes.OIA,
Sum(DISTINCTRoadsOIA.InspectionExtension) AS MonIE,
Sum(DISTINCTRoadsOIA_1.InspectionExtension) AS TueIE,
Sum(DISTINCTRoadsOIA_2.InspectionExtension) AS WedIE,
Sum(DISTINCTRoadsOIA_3.InspectionExtension) AS ThuIE,
Sum(DISTINCTRoadsOIA_4.InspectionExtension) AS FriIE
FROM DISTINCTRoadsOIA AS DISTINCTRoadsOIA_4
RIGHT JOIN (DISTINCTRoadsOIA AS DISTINCTRoadsOIA_3
RIGHT JOIN (DISTINCTRoadsOIA AS DISTINCTRoadsOIA_2
RIGHT JOIN (DISTINCTRoadsOIA AS DISTINCTRoadsOIA_1
RIGHT JOIN (DISTINCTRoadsOIA RIGHT JOIN tblRoutes
ON (DISTINCTRoadsOIA.OIA = tblRoutes.OIA) AND (DISTINCTRoadsOIA.[RoadDesignation] = tblRoutes.MON))
ON (DISTINCTRoadsOIA_1.OIA = tblRoutes.OIA) AND (DISTINCTRoadsOIA_1.[RoadDesignation] = tblRoutes.TUE))
ON (DISTINCTRoadsOIA_2.OIA = tblRoutes.OIA) AND (DISTINCTRoadsOIA_2.[RoadDesignation] = tblRoutes.WED))
ON (DISTINCTRoadsOIA_3.OIA = tblRoutes.OIA) AND (DISTINCTRoadsOIA_3.[RoadDesignation] = tblRoutes.THU))
ON (DISTINCTRoadsOIA_4.OIA = tblRoutes.OIA) AND (DISTINCTRoadsOIA_4.[RoadDesignation] = tblRoutes.FRI)
GROUP BY tblRoutes.ROUTE, tblRoutes.OIA;
ROUTE OIA MonIE TueIE WedIE ThuIE FriIE R-VRL1 jcferreira 69.806 81.275 28.494 31.978 21.961 R-VRL2 afteixeira 135.77 67.257 68.329 27.375 97.946 R-VRL2 jcferreira 69.806 81.275 28.494 31.978 66.041