"Consecutive days worked" query (1 Viewer)

opperman.eric · Sep 19, 2013

Hi

I have a table of employees, and dates they worked on. These are seasonal employees who want to get in as many days as possible before the season ends, but regulation states they must take a compulsory rest day after x days.

So I need to create a query that can return the list of employees, with a count of consecutive workdays up until current date.

If today is 20/09/2013, and Johnny worked on, 19, 18, 17, 15, 14, 13, his count must be 3, because he was absent on 16. Therefore only from 17 through 19 is regarded as consecutive.

If Peter worked 19, 18, 17, 16, 15, 14, 13, his count would be 7, because unlike Johnny, Peter still worked on 16.

Thanks, Eric

plog · Sep 19, 2013

Can you post the relevant table and field names?

opperman.eric · Sep 19, 2013

Hi

The table name is ImportData, and the field names are EmpNumber and StartDate.

Thanx!

plog · Sep 19, 2013

Your going to need a function in a module to do this. Paste the below code into a module in your database:

Code:

Function getDaysInARow(en, d) As Integer
    ' takes employee number (en) and determines how many prior days in a row prior to a date (d) they have worked
 
    Dim ret As Integer
    ret = 0
    ' return value of function, default is 0
 
    d = DateAdd("d", -1, d)
    ' sets d to first day before requested date--if worked will be counted as day 1
 
    Do While DCount("[EmpNumber]", "ImportData", "[EmpNumber]=" & en & " AND [StartDate]=#" & d & "#") > 0
    ' goes backwards day by day counting days in a row worked
 
                d = DateAdd("d", -1, d)
                ret = ret + 1
 
        Loop 
 
    getDaysInARow = ret
 
End Function

Then to use it in a query you would do it like so:

DaysWorkedInARow: getDaysInARow([EmpNumber],Date())

That will determine how many days in a row they have worked, starting with yesterday as the first day to count. You can change the second argument (Date()) to any date you want to determine how many days in a row they have worked prior to the date you provide:

February2013DaysWorkedInARow: getDaysInARow([EmpNumber],"3/1/2013")

The above will tell you how many days in a row they have worked starting with 2/28/2013 as day 1.

opperman.eric · Sep 19, 2013

Thanx! Looks good!

opperman.eric · May 15, 2015

Hi, can anyone help? The above works well in Access 2010, but when using the exact same data source, in 2013, the results seem to be limited to "16", even for employees with more. (In those cases, the correct number of days are returned in Access 2010)

I have checked the number of iterations setting, etc. I cannot find the problem

Galaxiom · May 15, 2015

I would try doing it all in a query. DAtabase engines are very good at summarising data without explicit iteration.

The key to this query is a self join. The table is joined to itself on the date field and a time period.

Start with a self join query like this:

Code:

SELECT * 
FROM Table AS Table_1 
INNER JOIN Table AS Table_2
ON table_1.datefield <= table_2.datefield + 5
AND table_1. EmployeeID = Table_2.EmployeeID

Now do a count on the number of records grouped on EmployeeID and Table_2.datefield

Voila. Results for all Employees for all possible sequences of five days in the data.

opperman.eric · May 18, 2015

Thanx Galaxiom. I like the function because it only gives me the number of consecutive days worked up until now. I.e. if an employee worked 100 days up to the day before yesterday, then had a day off, and worked again yesterday, I am only interested in an answer of one. Since he had the day off day before yesterday, the problem has been addressed. (This is to analyse clock-in data in a casual seasonal work environment, where people tend to report for work for more than the 21 consecutive days they are legally allowed)

Speaking of the function, it works in Access 2007, but if I take the application, with the EXACT SAME source data, to a computer running Access 2010 or 2013, the result is incorrect and too low. I created dummy data where 10 employees had consecutive clock-ins for more than 21 days leading up to current date. Each employee had a different count, but all more than 21. On one PC, I got a result of 16 days for all of them using Access 2013. On another PC running 2010 I got an answer of 5 for all employees. On yet another PC running 2013 I also got an answer of 5 for all.

Galaxiom · May 19, 2015

The query will also work with a Where clause to limit it to one employee.

It could be turned into a function too.

opperman.eric · May 19, 2015

Thanx. Limiting it to one is not feasible, as I need to run the report for between 600 and 1000 staff. At any given time 50+ are encroaching the maximum allowable consecutive days worked. I wouldn't know which 50, hence this query.

It works. I can simply continue to run it on a machine with access 2007. I am more interested in why 2010 and 2013 would terminate the DO WHILE before conditions are met, and 2007 doesn't.

What can I change in 2013 to make the DO WHILE work correctly?

Galaxiom · May 19, 2015

opperman.eric said:
Thanx. Limiting it to one is not feasible, as I need to run the report for between 600 and 1000 staff.

Which is it to be? The function operates on a single employee at a time and you like it. But my query is not practical reduced to one employee? The query can be run for any number of employees and any date period by simply adjusting the Where criteria.

Moreover, the query will return all the results long before that kludge of a function calls tens of thousands of DCounts. Even if the query was configured as a function to process a single employee and date it would still be several times faster than Plog's function. I could hardly think of a less efficient way to do the job.

It works. I can simply continue to run it on a machine with access 2007. I am more interested in why 2010 and 2013 would terminate the DO WHILE before conditions are met, and 2007 doesn't.

What can I change in 2013 to make the DO WHILE work correctly?

Are you sure the difference is the version of Access? I can see some serious problems in the function that could cause unexpected results.

Firstly the date needs to be formatted mm/dd/yyyy for the DCount unless that is your system Regional Date format. This oversight could definitely cause inconsistent results where dates are ambiguous.

The most dangerous problem is the decrementing of the d parameter within the function. Because it has been passed undeclared it will be passed ByRef (the VBA default). Consequently the original date parameter passed in the call will be decremented along with it in the function potentially corrupting where it comes from.

Solo712 · May 19, 2015

Galaxiom said:
Which is it to be? The function operates on a single employee at a time and you like it. But my query is not practical reduced to one employee? The query can be run for any number of employees and any date period by simply adjusting the Where criteria.

Moreover, the query will return all the results long before that kludge of a function calls tens of thousands of DCounts. Even if the query was configured as a function to process a single employee and date it would still be several times faster than Plog's function. I could hardly think of a less efficient way to do the job.

Are you sure the difference is the version of Access? I can see some serious problems in the function that could cause unexpected results.

Firstly the date needs to be formatted mm/dd/yyyy for the DCount unless that is your system Regional Date format. This oversight could definitely cause inconsistent results where dates are ambiguous.

The most dangerous problem is the decrementing of the d parameter within the function. Because it has been passed undeclared it will be passed ByRef (the VBA default). Consequently the original date parameter passed in the call will be decremented along with it in the function potentially corrupting where it comes from.

In addition, the function argument types are not defined, ie are variants.
Do instead

Code:

getDaysInARow(en as Long, d as Date) As Integer

and

Code:

Do While DCount("[EmpNumber]", "ImportData", "[EmpNumber]=" & en & " AND [StartDate]=#" & Format(d, "mm/dd/yyyy") & "#") > 0

Best,
Jiri

Galaxiom · May 20, 2015

Solo712 said:
Code:

getDaysInARow(en as Long, d as Date) As Integer

Still not enough because it passes the parameters ByRef and as such any change to them in the function is passed back to the caller.

The damage can be done by this line which will decrement the value of d passed to the function.

Code:

d = DateAdd("d", -1, d)

The function should be defined like this:

Code:

getDaysInARow(ByVal en as Long, ByVal d as Date) As Integer

It is best practice to always declare how the parameters are passed. ByVal ensures the parameter will not be affected and makes that clear to anyone maintaining the code. Conversely ByRef alerts that changes are intentionally being passed back.

Note however that arrays and objects are always passed ByRef no matter what is declared. Any ByVal declaration is silently ignored in these cases.

I would also recommend that arguments be named more meaningfully.

"Consecutive days worked" query (1 Viewer)

opperman.eric

New member

plog

Banishment Pending

opperman.eric

New member

plog

Banishment Pending

opperman.eric

New member

opperman.eric

New member

Galaxiom

Super Moderator

opperman.eric

New member

Galaxiom

Super Moderator

opperman.eric

New member

Galaxiom

Super Moderator

Solo712

Registered User.

Galaxiom

Super Moderator

Similar threads

Users who are viewing this thread