Can join TASK DESIGNATION field to each day field. Have to first change TASK DESIGNATION to short text field.
Code:
SELECT tblRoutes.*,
tblTasks.[INSPECTION EXTENSION] AS MonIE,
tblTasks_1.[INSPECTION EXTENSION] AS TueIE,
tblTasks_2.[INSPECTION EXTENSION] AS WedIE,
tblTasks_3.[INSPECTION EXTENSION] AS ThuIE,
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;
Really should be saving IDTASK as foreign key in number type day fields instead. Otherwise, TASK DESIGNATION should be defined as primary key because not using IDTASK for that purpose.
Saving OIA data into tblRoutes is duplication.
Advise not to use spaces in naming convention and all upper case is harder to read. Better would be camel case: TaskDesignation.
I inherited a db that used Excel as GUI (data entry/edit and reporting). The reason for Excel was also based on "lots of calculations". After 3 months I decided to eliminate Excel and put everything in Access. Much happier dealing with only one software.
Attaching files is not restricted by number of posts but I thought posting links was (as spam deterrent). Maybe the case in another site.
Windows 10 
Office 365
Reply With Quote
Originally Posted by Welshgasman
