Count Consecutive Only

[WickidWe]

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

In the above query I have the Count how many times an Employee between two given dates does a specific GroupID.
is there a way to count how many consecutive days the employee does GroupID=1 after their last GroupID NOT IN 1?

08/12/13 Jo, Bloggs Group=1
09/12/13 Jo, Bloggs Group=2
10/12/13 Jo, Bloggs Group=1

11/12/13 Jo, Bloggs Group=1
12/12/13 Jo, Bloggs Group=1

in the above scenario the consecutive count would be 3 as only the last 3 are group=1

[June7]

Maybe more VBA and temp table like in your other thread https://www.accessforums.net/queries...ely-39890.html

[WickidWe]

it is still the tbl_Cons that is throwing me

tbl_Cons
Cons_ID (autonumber PK)
Person_ID (number, FK to emoployee table)
Start_Date (date, entered by user)
End_Date (date, figured by calculation)
Function_ID (number, FK to function table)
Cons_Days (maximum consecutive days)

because I havent been able to properly create it the code stops at
db.Execute ("DELETE * FROM tbl_Cons")

[June7]

Why can't you build table? Just open the table designer and create fields and save the table.

[WickidWe]

Ok, so I created the Cons Table I dont Know why I thought it needed to be a temp table, but anyway...
My debugger is coming up at this point in the image, any Ideas?

[June7]

'Temp table' means the records are temporary, the table is permanent.


What is the error message?

[WickidWe]

I am now trying to run it from my PC at home I created the tables exactly as you have set them up
and I created a form with a single command button to run the script
I am still getting the debug screen popping up

[June7]

Is EmpID a text field? Should it be a Number type?

Text fields need criteria enclosed with apostrophe delimiters.

WHERE EmpID = '" & iPers & "'"

[WickidWe]

I put that in, it is now bringing up the EmpID (as in the error message) but now there is a different syntax error

[June7]

Too many " marks.

"SELECT * FROM tblRosterRec WHERE EmpID = '" & iPersonID & "' AND Date_ BETWEEN #" & dStartDate & "# AND #" & dEndDate & "# ORDER BY Date_ DESC;"

[WickidWe]

I changed a few things in there to suit my fields and tables in my case a query,
now I am not coming up with any errors but I am also not coming up with any results eather.
there should be one employee with a count of 5.

here is the modified version of the code you gave me

Option Compare Database
Dim dStartDate As Date
Dim iProjDays As Integer
Dim dEndDate As Date
Dim db As Database
Dim sSQL As String
Dim rst_People As Recordset
Dim iPersonID As Long
Dim rst_Functions As Recordset
Dim dFunDate As Date
Dim dFunDatePrev As Date
Dim iFun As Long
Dim iFunSearch As Long
Dim iCons As Integer
Dim iConsMax As Integer
dStartDate = [Forms]![testConCount]![txtDate] 'InputBox("Enter the Starting Date", "Enter the Starting Date of the Reporting Period", #1/1/2013#)
'iProjDays = InputBox("Report through how many days?", "Enter the Number of Days following the start date", 7)
dEndDate = [Forms]![testConCount]![txtDate2] 'DateAdd("d", iProjDays, dStartDate)
Set db = CurrentDb
sSQL = "SELECT EmpID FROM qryConCount WHERE Date_ Between #" & dStartDate & "# AND #" & dEndDate & "# GROUP BY EmpID"
Set rst_People = db.OpenRecordset(sSQL)
If rst_People.RecordCount <> 0 Then
db.Execute ("DELETE * FROM tbl_Cons")
Do While rst_People.EOF <> True
iPersonID = rst_People.Fields("EmpID")
'Debug.Print iPersonID
sSQL = "SELECT * FROM qryConCount WHERE EmpID = " & iPersonID & " AND Date_ Between #" & dStartDate & "# AND #" & dEndDate & "# ORDER BY Date_ DESC"
Set rst_Functions = db.OpenRecordset(sSQL)
rst_Functions.MoveFirst
iFunSearch = rst_Functions.Fields("GroupID") 'sets the function ID we're looking to count consecutive days for
dFunDatePrev = rst_Functions.Fields("Date_") 'initializes the date to start counting
iCons = 1 'initializes the consecutive day count
iConsMax = 1
Do While rst_Functions.EOF <> True 'cycles through function records from the most recent to the least recent over the defined period
dFunDate = rst_Functions.Fields("Date_")
iFun = rst_Functions.Fields("GroupID")
If iFun = iFunSearch Then 'if the function date matches the function of the most recent day, keep going
If dFunDate = DateAdd("d", -1, dFunDatePrev) Then 'if the date of the current record is one day prior to the last record add 1 to the consecutive count
iCons = iCons + 1 'increments the consecutive days by 1 if it's a consecutive day
If iCons > iConsMax Then
iConsMax = iCons 'if the current consecutive days is greater than the currrent maximum consecutive days make the maximum consecutive days = current consecutive days
End If
Else
iCons = 1 'if the date of the current record is NOT one day prior the last record, reset the current consecutive days count to 1
End If
dFunDatePrev = dFunDate
Else 'if the function date DOES NOT match the function of the most recent day, move to the last record to exit the function loop
rst_Functions.MoveLast
End If
rst_Functions.MoveNext
Loop
rst_Functions.Close
sSQL = "INSERT INTO tblCons ("
sSQL = sSQL & "EmpID, "
sSQL = sSQL & "Start, "
sSQL = sSQL & "End, "
sSQL = sSQL & "GroupID, "
sSQL = sSQL & "ConsDays"
sSQL = sSQL & ") VALUES ("
sSQL = sSQL & iPersonID & ", "
sSQL = sSQL & "#" & dStartDate & "#, "
sSQL = sSQL & "#" & dEndDate & "#, "
sSQL = sSQL & iFunSearch & ", "
sSQL = sSQL & iConsMax
sSQL = sSQL & ")"
db.Execute (sSQL)

rst_People.MoveNext
Loop
rst_People.Close
Else
MsgBox "no records in the range specified", vbOKOnly, "error: no records in range"
End If
Set db = Nothing

[June7]

Step debug. Follow the code as it executes. See if variables are populated.

Refer to link at bottom of my post for debugging guidelines.

[WickidWe]

I am now stopping here

[June7]

I think that means the recordset is empty, no records met the criteria or the recordset is a forward-only type and none of the movement methods will work.

Review
http://ms-access-tips.blogspot.com/2...-vba-code.html
http://allenbrowne.com/ser-29.html