Counting Sequential Repeats
I'm new to FileMaker and could use some help with counting sequential repeats. Here's what I mean: I have a table called "Daily Work" which has the following fields: Date, Client ID, Employee ID (note that there is a many-to-many relationship between Client ID, and Employee ID)
I need to generate a report for each employee which shows how many days in a row the employee worked for a particular client before there was a break in service for that client. Example:
REPORT FOR EMPLOYEE JOE SMITH
Client ID Last Date in Sequence Number of Times in a Row before break
ClientA, 1/5/2013, 4 [e.g., Joe worked for ClientA on 1/2/2013, 1/3, 1/4, and 1/5 but not 1/6]
ClientA, 1/14/2013, 5 [e.g., Joe took a break from ClientA then did work again for ClientA on 1/10, 1/11, 1/12,1/13, and 1/14 but not 1/15]
ClientB, 1/7/2013, 2 [e.g., Joe worked for ClientB on 1/6 and 1/7 but not on 1/8]
ClientB, 1/29/2013, 1 [e.g., Joe took a break from ClientB then did work again for ClientB on 1/29 but didn't work for ClientB on 1/30]
I also need to do the same thing but this time by week. In otherwords, so long as if the employee did any work for the client during any calendar week, then the report would count that calendar week as a "week worked" for that Client. Then, I need to list the number of sequential weekly repeats [ending at the week where the employee did nothing for that client].
Can anyone give me an idea how do I do this? Thanks!