Finding Consecutive Workdays with Query ~ LEARN MS-ACCESS TIPS AND TRICKS

Friday, April 18, 2008

Finding Consecutive Workdays with Query

Introduction.

How can we determine whether date values within a certain range are consecutive or intermittent?

Let’s use a real-world example:

A company hires temporary workers on a daily wage to complete a project within 15 days. Workers are informed that if they work 8 hours a day for 7 consecutive days (no weekends or breaks), they will receive a special incentive in addition to their daily wages.

In practice, employees joined on different dates, and not all could maintain a full 7-day streak. Attendance is logged in a table by date and employee code.

After the project ends, we are tasked with identifying which employees worked 7 straight days to award the incentives.

This is actually quite simple to solve—just three queries are enough.

A Table with Sample Data.

Step 1 – Table Structure

Create a new table in Design View with the following fields:

Field Name	Data Type	Description
emp	Short Text	Employee code (e.g., E001, E002)
workdate	Date/Time	The date the employee worked

Step 2 – Primary Key

Select both fields (emp and workdate) together.
Right-click → Primary Key.
This ensures no duplicate date entries are accepted for the same employee.

Solution

Query1:
Create a Total Query Grouped by emp, take the minimum value from workdate to find the work-start date of each employee, and create a new column, adding 6 days to the work-start date to calculate and store the 7th Day-Date of work for each employee. They must work from the work-start date to this date without a break for special incentive eligibility.

SQL: Query1

SELECT Table1.emp,
 Min(Table1.workdate) AS min_date,
 Min([workdate])+6 AS Day7Date
FROM Table1
GROUP BY Table1.emp;

Query2: Join Table1 with Query1 on the emp field and select records using the Day7Date as a criterion on workdate that falls on or before the Day7Date.
SQL: Query2
```
SELECT Table1.* 
FROM Query1 INNER JOIN Table1 ON Query1.emp = Table1.emp
WHERE (((Table1.workdate)<= [Day7Date]));
```
Query3: Create a Total Query using Query2 as a source. Group on 'emp' field, take Count of workdate, and filter the output with workdate Count =7.

SQL: Query3

SELECT Query2.emp,
 Count(Query2.workdate) AS [count]
FROM Query2
GROUP BY Query2.emp
HAVING (((Count(Query2.workdate))=7));

The continuity of dates is validated at the employee level, based on each employee’s individual work start date. All employees don't need to work within the same month or start on a specific date.

However, when the data file contains a large number of records spanning different periods, and only records from a particular period need to be evaluated using this method, it’s important to first filter the data. To do this, create a query using Table1, applying the desired Start Date and End Date as criteria to extract only records within that range. Then, use this filtered query as the source for Query1 and Query2 described earlier, instead of referencing Table1 directly.

As the saying goes, “There’s more than one way to skin a cat,” we can take an alternative approach to solve this problem by slightly differentiating how the earlier queries are defined. The SQL strings for this alternative method are provided below. Copy them and create the queries to try out this method as well.

Query 1_1 SQL

SELECT Table1.emp,
 Min(Table1.workdate) AS min_date,
 [min_date]+0 AS day1,
 [min_date]+1 AS day2,
 [min_date]+2 AS day3,
 [min_date]+3 AS day4,
 [min_date]+4 AS day5,
 [min_date]+5 AS day6,
 [min_date]+6 AS day7
FROM Table1
GROUP BY Table1.emp;

Query 2_1 SQL

SELECT Table1.emp,
 Table1.workdate
FROM Query1_1 INNER JOIN Table1 ON Query1_1.emp = Table1.emp
WHERE (((Table1.workdate) In ([day1],[day2],[day3],[day4],[day5],[day6],[day7])));

Query 3_1 SQL

SELECT Query2_1.emp,
 Count(Query2_1.workdate) AS CountOfworkdate
FROM Query2_1
GROUP BY Query2_1.emp
HAVING (((Count([Query1_2].[workdate]))=7));

I have not tested these methods extensively to eliminate side effects. You may use them at your own risk.

Do you have different ideas? Please share them with me by posting them in the Comments Section.

LEARN MS-ACCESS TIPS AND TRICKS

Friday, April 18, 2008