Contractors churn rate

[JohnnyLong]

Hi, I have been asked to produce a query showing the churn rate for our contractors. We have a timesheet table which records their weekly timesheets, the week number and date of each timesheet. I need to show the length in weeks of each contractors assignment with any break between timesheets of more than 3 weeks indicating a new assignment. I then need to show the average assignment length over all contractors. The query is for all timesheets from 01/01/2006 to Now.

Surname Firstname Week Date
Smith John 1 01/01/2006
Smith John 2 08/01/2006
Smith John 3 15/01/2006
Smith John 4 22/01/2006
Doe Jane 3 15/01/2006
Doe Jane 4 22/01/2006
Doe Jane 5 29/01/2006
Doe Jane 9 26/02/2006
Doe Jane 10 05/01/2006

So the results for the above would show:
Surname Firstname ContractLength
Smith John 4
Doe Jane 3
Doe Jane 2

Not even sure where to start! I am using Access 2003.

Any help greatly appreciated.

Thanks,

John

 

[JohnnyLong]

Don't worry guys, problem solved.

 

[mkrausnick]

Maybe you could share your solution so someone later on with a similar problem can learn?

Mike Krausnick
Dublin, California