Query between two tables

[JCabral]

June7 / mike60smart

These are the expected results for the case of the query I mentioned: OIA="afteixeira" and ROUTE="R-VRL1"

OIA	ROUTE	MonIE	TueIE	WedIE	ThuIE	FriIE
afteixeira	R-VRL1	82,112	70,166	158,226	14,230	37,467

Thank you very much

[June7]

Using data in posted database:

SELECT tblTasks_4.OIA, tblRoutes.ROUTE, 
Sum(tblTasks.[INSPECTION EXTENSION]) AS MonIE, 
Sum(tblTasks_1.[INSPECTION EXTENSION]) AS TueIE, 
Sum(tblTasks_2.[INSPECTION EXTENSION]) AS WedIE, 
Sum(tblTasks_3.[INSPECTION EXTENSION]) AS ThuIE, 
Sum(tblTasks_4.[INSPECTION EXTENSION]) AS FriIE
FROM tblTasks AS tblTasks_4 
RIGHT JOIN (tblTasks AS tblTasks_3 
RIGHT JOIN (tblTasks AS tblTasks_2 
RIGHT JOIN (tblTasks AS tblTasks_1 
RIGHT JOIN (tblTasks RIGHT JOIN tblRoutes 
ON tblTasks.[TASK DESIGNATION] = tblRoutes.MON) 
ON tblTasks_1.[TASK DESIGNATION] = tblRoutes.TUE) 
ON tblTasks_2.[TASK DESIGNATION] = tblRoutes.WED) 
ON tblTasks_3.[TASK DESIGNATION] = tblRoutes.THU) 
ON tblTasks_4.[TASK DESIGNATION] = tblRoutes.FRI
GROUP BY tblTasks_4.OIA, tblRoutes.ROUTE;

OIA	ROUTE	MonIE	TueIE	WedIE	ThuIE	FriIE
afteixeira	R-VRL1	77.292	79.216	115.026	19.329	60.367

I am in the U.S. so database shows period for decimal instead of comma.

You can either do the aggregation in query as shown above or use the earlier query I posted as source for a report and then use report Sorting & Grouping features with Sum() aggregate calc in textboxes. The advantage of report is can show detail data as well as summary info.

[mike60smart]

Hi

Does the following rptTasks now make more sense?

[mike60smart]

As previously mentioned by Welshgasman, the tables are not normalised so you
will have difficulty getting the required result.

Are you able to upload the actual Excel File?

[JCabral]

Hi June7
Thank you very much for your answer and for your help.


I think there must be some problem with this new query that you posted. I think, or I'm doing the wrong calculations, or the query calculations aren't quite right.
So in the figure below I show the value of "[INSPECTION EXTENSION]" for each task, from ROUTE , "R-VRL1" and the total for each day of the week and it does not equal yours.



Furthermore, when running the query in BD_TASKS, OIA values without data appear (their names) and the values of ROUTES "R-VRL1" and "R-VRL2" ; "F-Férias_1" and "F-Férias_2", for example, are the same, which cannot be.

[JCabral]

Hi mike60smart
Thank you very much for your answer and for your help.


I think that the only report that will make sense for what I want will be something that shows only and only the following data:

[mike60smart]

Attachment does not display

[JCabral]

Attachment does not display

And now it displays?

[mike60smart]

Hi

Please look at the attached Excel file and explain how you get the value of 1,555 for TUE ?

[JCabral]

Hi

Please look at the attached Excel file and explain how you get the value of 1,555 for TUE ?

As you can see in tblTasks the task "TASK #0010" has an "INSPECTION EXTENSION" of 1,555 .
You have to put three decimal places

[mike60smart]

This cannot be achieved due to your incorrect table structures

[June7]

Ooops, my goof in posted query. Change so OIA is pulled from tblRoutes instead of tblTasks_4.

OIA	ROUTE	MonIE	TueIE	WedIE	ThuIE	FriIE
afteixeira	R-VRL1	82.112	70.166	158.226	14.23	37.467
afteixeira	R-VRL2	82.112	70.166	167.001	14.23	37.467

A UNION query can rearrange to normalized structure.

SELECT IDROUTE, OIA, NROUTE, ROUTE, MON AS Data, "MON" AS Dy FROM tblRoutes WHERE NOT MON IS NULL
UNION SELECT IDROUTE, OIA, NROUTE, ROUTE, TUE, "TUE" FROM tblRoutes WHERE NOT TUE IS NULL
UNION SELECT IDROUTE, OIA, NROUTE, ROUTE, WED, "WED" FROM tblRoutes WHERE NOT WED IS NULL
UNION SELECT IDROUTE, OIA, NROUTE, ROUTE, THU, "THU" FROM tblRoutes WHERE NOT THU IS NULL
UNION SELECT IDROUTE, OIA, NROUTE, ROUTE, FRI, "FRI" FROM tblRoutes WHERE NOT FRI IS NULL;

Then a CROSSTAB of joined tables produces the same output:

TRANSFORM Sum(tblTasks.[INSPECTION EXTENSION]) AS [SumOfINSPECTION EXTENSION]
SELECT RoutesUNION.OIA, RoutesUNION.ROUTE
FROM RoutesUNION INNER JOIN tblTasks ON RoutesUNION.Data = tblTasks.[TASK DESIGNATION]
GROUP BY RoutesUNION.OIA, RoutesUNION.ROUTE
PIVOT RoutesUNION.Dy IN ("MON", "TUE", "WED", "THU", "FRI");

A simple join query of the UNION to tblTasks reveals inconsistency in assignment of OIA values. What purpose does it serve to have in tblTasks?

SELECT RoutesUNION.IDROUTE, tblTasks.OIA, RoutesUNION.OIA, RoutesUNION.ROUTE, tblTasks.[TASK DESIGNATION], RoutesUNION.Data, RoutesUNION.Dy, tblTasks.[INSPECTION EXTENSION]
FROM RoutesUNION INNER JOIN tblTasks ON RoutesUNION.Data = tblTasks.[TASK DESIGNATION]
ORDER BY tblTasks.OIA, RoutesUNION.OIA, RoutesUNION.ROUTE, RoutesUNION.Dy;

IDROUTE	tblTasks.OIA	RoutesUNION.OIA	ROUTE	TASK DESIGNATION	Data	Dy	INSPECTION EXTENSION
116	afteixeira	jcferreira	F-Férias_1	TAREFA #0028	TAREFA #0028	FRI	3.14

I repeat, IDTASK is assigned as primary key but it is not being saved into tblRoutes as foreign key. This is a design flaw.

[JCabral]

This cannot be achieved due to your incorrect table structures

June7 is there, thanks mike60smart

[Micron]

It may be possible to solve with a lot of work, which may end up being a code solution. However, I know it's not attractive (to me at least) to invest so much time into something that could be avoided by proper table design and relationships. You stated in bold that you cannot change this, and whenever I see that comment I think it's fair to ask why not given that you wish someone to put in a lot of effort to achieve a desired result from incorrectly designed tables. If the reason you cannot use a proper design is your boss, then that's not a good enough reason. He/she needs to relent, or give up the notion of doing this in Access. What you want is probably far easier to get out of Excel.

[JCabral]

june7
Thank you very much for your answer and for your help.


I think the problem for the data that I put here in the forum is solved , I'm having trouble translating it to my real tables, which I only noticed later, which have a "slight" difference that may be the cause of not getting your query works, the mistake was mine.

What is happening is that in the table "tblTasks" that is in the file that I put here the "TASK DESIGNATION" are all different, which in reality is not true, they can be repeated for different OIA's. that is, although for each "TASK DESIGNATION" the data is unique, and always the same no matter which OIA it is, a certain TASK DESIGNATION, for example, "TASK #0010" can appear in OIA "A" and in OIA "B" , I don't know if this is what is affecting the results in the real tables.

I'm going to do tests and if I don't get it I'll come back here to ask for help.


As for the rest you put in #27 I think it was to reply to mike60smart, because unfortunately I don't have the slightest idea what you're talking about