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.
Code:
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:
Code:
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?
Code:
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.