This one has been puzzling me, but there is probably a simple solution out there that I have missed..

We have a table with all our staff, a table for shifts, and a table for dates in turn related to a table of jobs.

Each staff member can have many shifts on many different dates.

We want to pull a report that shows all staff that have worked their first ever shift within a date range we enter.

I've thought about putting a first shift field within the shift table, which when their first shift is created it enters a one into this field - enabling us to search this field. However this doesn't work if there first shift is canceled or deleted after a second shift has been entered for them.

Any help would be greatly appreciated!

Thanks,

Dan

We want to pull a report that shows all staff that have worked their first ever shift within a date range we enter.

I've thought about putting a first shift field within the shift table, which when their first shift is created it enters a one into this field - enabling us to search this field. However this doesn't work if there first shift is canceled or deleted after a second shift has been entered for them.

In this solution, the “first shift” field is a calculation field in the Employee table, which calculates the Min () for all shifts of each employee. If you don't delete canceled shifts, but set them inactive, you need a filtered relationship to retrieve the correct Min () value.

HTH

FirstShiftIndateRange.fp7.zip

Make the Shifts table a join table between Staff and Jobs. It should contain StaffID, JobID, and Date. It should have a record for every shift worked by every staff member. Then a calculation field FirstShift (=min(Shift::Date) ) can be created in Staff. You can then find when FirstShift is between global gStartDate and gEndDate fields created in Staff.

I don't understand the last sentence. If a shift is cancelled, it can't possibly be the staff member's first shift. And if s/he worked it, why should it be deleted?

Also, what is the purpose of the Dates table?