Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Finding Consecutive Workdays with Query

Introduction.

How do we determine whether the date values within a certain date range are consecutive or intermittent? To bring some clarity to this problem and solution I will present an example below.

Assume that a Company hires some temporary employees on daily wages to complete a Project within 15 days' time. The Company informs the temporary hands that if they work 8 hours a day and for 7 days continuously without taking weekend breaks they will be rewarded with special incentives besides their daily wages.

The employees joined to work on different dates and not all of them could work for 7 days without breaks. The attendance of the employees is logged date-wise into a Table with Employee Codes.

After completion of the project, the task was given to us to prepare the list of employees who worked continuously for 7 days for awarding their declared incentives.

I know it sounds like a Question in an Examination Hall. But, we are faced with difficult questions very often, while considering data processing tasks, and most of them we have to ask ourselves to find solutions.

This is not that hard to find. With three simple Queries, we can solve this problem. Interested? Read on.

A Table with Sample Data.

First, we need a table with some sample data. Create a Table with the name Table1 with the following Table Structure and enter sample data into it from the image given below. You may add more employee Codes and Date Values on similar lines for more accurate testing. Define emp and the workdate fields combined as Primary Key to avoid entry of duplicate date-values.

Solution

  1. 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 eligibility for special incentives.

    Query1 SQL :

    SELECT Table1.emp,
     Min(Table1.workdate) AS min_date,
     Min([workdate])+6 AS Day7Date
    FROM Table1
    GROUP BY Table1.emp;
    
  2. 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.

    Query2 SQL :

    SELECT Table1.* 
    FROM Query1 INNER JOIN Table1 ON Query1.emp = Table1.emp
    WHERE (((Table1.workdate)<= [Day7Date]));
    
  3. 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.

Query3 SQL:

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

Note: The continuity of dates is checked at the employee level based on their work-start dates. Not necessary that all of them should work within the same month or start work on a specific Date.

But when the data file contains a large number of records for various periods, and the record pertains to a specific period needs to be subjected to this method, then data filtering must be done. Create a Query using Table1 and use the Start-Date and End-Date range as criteria to pick records within that period. Use that Query as source data for Query1 and Query2 above, instead of using Table1 directly.

As the saying goes - "There is always more than one way to skin a Cat" (did I say it correctly), we can take a different approach to solve this problem with a slight deviation to the definition of the above Queries. The Query SQL strings of this new method are also given below. You may copy them and create Queries to try them out too.

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 if any. 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.


Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code