Query between two tables

[June7]

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

[JCabral]

Hi June7

Your results are totally correct, they reflect the values of the new tables and the arrangement with the names of the tasks.


The problem of the Districts I cannot solve at the moment, although I believe that at the moment it is not an obstacle, for the problem in question.


The bugs in the Inspection extensions [InspectionExtension], which you noticed, are fixed.


As for the "errors" in the weekly frequency [WeeklyFrequency], as I said, they can vary depending on whether it is a normal route or a vacation route, but what is important here is the value of [WeeklyObligation].


From post #46 I see that I have to do two queries, I don't know how I'll do it via Excel, but I'm going to study it, however I have a question to ask that maybe solves my problem, if Query1: DISTINCTRoadsOIA, is in the BD_Roads.accdb file is it updated whenever I make changes to tblTasks?
If so then via Excel I just have to do Query2.


As for the query, to be made for a specific OIA, I'll do a test and if it works, I'll post it here.


Thank you so much again

[June7]

Yes, query will reflect changes to table.

Build both queries in Access and Excel can pull data from query 2.

Why do you need to involve Excel?

[JCabral]

I need Excel because i have an huge amount of calculations to do. A lot of formulas based on planning and inspections made