Ok, you have many problems that you have to solve:
1) You only need two tables: BlueShiftPattern and tbl_Absence
2) All the dates need to be defined as Date/Hour and format short date (Sorry the images are in spanish version)
3) The format (Text) that you capture de dates: dd/mm/yyyy, let me asume that you arenīt in US, I have the same problem, because ACCESS use de format mm/dd/yyyy, in this case, we have to use a fuction FORMAT, to change the date from dd/mm/yyyy to mm/dd/yyyy in the DCONT fuction.
With this changes, the solution is Ok.
Sorry I don't know what is the working day, the blue or the red.
first try to open and execute the solution that I send to you, if you have any problem, continue with the next option
Date Fnction New.zip
Once again open your query, change to view in sql, select al the text, remove it, y copy the next one
SELECT tbl_Absence.[Employee ID], tbl_Absence.[Shift Pattern], tbl_Absence.[Start Date], tbl_Absence.[End Date], DateDiff("d",[Start Date],[End Date])+1-DCount("[Date_Blue]","BlueShiftPattern","[BlueShiftPattern]![Date_Blue] between #" & Format([tbl_Absence]![Start Date],"mm/dd/yyyy",2) & "# and #" & Format([tbl_Absence]![End Date],"mm/dd/yyyy",2) & "#") AS Absents, -DCount("[Date_Blue]","BlueShiftPattern","[BlueShiftPattern]![Date_Blue] between #" & [tbl_Absence]![Start Date] & "# and #" & [tbl_Absence]![End Date] & "#") AS Blue_count
FROM tbl_Absence;