Count values that occur consecutively

[wickidwe]

Problem description: How to count the amount of consecutive of a specific group. after an alternate group. It needs to be the last occurance only of each group See example below

Data

DATE | NAME | GROUP
08/12/13 | Jo, Bloggs | 1
09/12/13 | Jo, Bloggs | 1
10/12/13 | Jo, Bloggs | 2
11/12/13 | Jo, Bloggs | 1
12/12/13 | Jo, Bloggs | 1

Result:

NAME | GROUP | CONSECUTIVE_DAYS_PER_GROUP
Jo, Bloggs | 1 | 2
Jo, Bloggs | 2 | 1
Reason: 2 consecutive days after group 2 occured for group 1.
and 1 day after the previous group 1 occured for group 2

Another example:


DATE | NAME | GROUP
08/12/13 | Jo, Bloggs | 1
09/12/13 | Jo, Bloggs | 2
10/12/13 | Jo, Bloggs | 2
11/12/13 | Jo, Bloggs | 1
12/12/13 | Jo, Bloggs | 1

Result:

NAME | GROUP | CONSECUTIVE_DAYS_PER_GROUP
Jo, Bloggs | 1 | 2
Jo, Bloggs | 2 | 2

Another example:


DATE | NAME | GROUP
05/12/13 | Jo, Bloggs | 1
06/12/13 | Jo, Bloggs | 1
07/12/13 | Jo, Bloggs | 1
08/12/13 | Jo, Bloggs | 1
09/12/13 | Jo, Bloggs | 2
10/12/13 | Jo, Bloggs | 2
11/12/13 | Jo, Bloggs | 3
12/12/13 | Jo, Bloggs | 2
13/12/13 | Jo, Bloggs | 1
14/12/13 | Jo, Bloggs | 1


Result:

NAME | GROUP | CONSECUTIVE_DAYS_PER_GROUP
Jo, Bloggs | 1 | 2
Jo, Bloggs | 2 | 1
Jo, Bloggs | 3 | 1

here is my query as it stands now.

qryConsec

SELECT qrr.name, qrr.groupid, Count(groupid) AS [Count]
FROM qrydetailsRosterRec AS qrr
GROUP BY qrr.name, qrr.groupid;

qrydetailsRosterRec

SELECT tblEmpDetails.Name, tblFctnGroup.GroupID, tblFunctions.ReliefCode, tblRosterRec.Date_
FROM ((tblFctnGroup INNER JOIN tblFunctions ON tblFctnGroup.FctnGroup = tblFunctions.FctnGroup) INNER JOIN tblRosterRec ON tblFunctions.ReliefCode = tblRosterRec.FctnID) INNER JOIN tblEmpDetails ON tblRosterRec.EmpId = tblEmpDetails.EmpId
WHERE (((tblRosterRec.Date_)<=[Forms]![Form1]![txtDate] And (tblRosterRec.Date_)>=[Forms]![Form1]![txtDate2]))
GROUP BY tblEmpDetails.Name, tblFctnGroup.GroupID, tblFunctions.ReliefCode, tblRosterRec.Date_;