Switches / Flip Flops / Iterations

Hi Folks,

I am wondering what is the best way to tackle this request and am really not sure. I am trying to determine the number of what I call flip flop interations. An individual can be in group a or group b at any given time, but for each day of service on record must be in at least one of the two groups. The individual can switch between groups as well. I am attempting to determine that number of times an individual switches in a 3 year period. For example:

Person #1 Sorted by Date of Service:

Feb 20, 2006 - Group A
Feb 22, 2006 - Group A
March 10, 2006 - Group A
Jan 4, 2007 - Group B
April 1, 2007 - Group A

So the above individual switched twice, starting at Group A.

So the result for this individual may be something like:


IDStart GroupFlip Flops

Person#1 Group A 2 Times

If someone can give me any ideas how to tackle this, I would greatly appreciate it. The database is pretty large with about 35,000 unique clients and some of them never flip flop but other can up to 13 times per year, and this is for 3 year time period. Even if I can just get the # of times an individual flip flopped will help tremendously, but the start group would be nice as well. I know there is a way to do this with a query, but I am just not sure where to start ...

Thank you very much,

Joe