There is no relations to show in this case. It is a declaration of work for 28 days - in place of a duty number, on each given day an employee can select AH or SK (Holiday or Sick), and if they appear I need to count them in seven day blocks.
The payment differential is simply looked up from another table, multiplied by the number of days to give a total pay adjustment; none of these values are stored.
To give a simplifed example, a user will input their duty number or SK/AH, so for a week, the record could potentially show:
(FieldName - Data)
SatDuty - 22
SunDuty - 21
MonDuty - 22
TueDuty - AH
WedDuty - AH
ThurDuty - Rest
FriDuty - Rest
So, for the total number of days AH I would need the count to show 2 for this week
Only the duty number is held - the duty information (start time, end time, shift allowance payment etc) are looked up from the duties table.
I did wonder if I could handle this by an after update event on the combo where the duty is selected, to add '1' to the week value in the totals field if AH was the value, but how would I then remove that if someone changed AH to a duty number (if they are called back from a holiday etc)?