Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Finding Consecutive Workdays with Query

How do we determine that the date values within a certain date-range are consecutive or intermittent? To bring some clarity into 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 week-end breaks they will be rewarded with special incentives besides their daily wages.

The employees joined for 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.

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 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 break for eligibility of special incentive.

    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 emp field and select records using the Day7Date as 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 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 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 large amount of records for various periods and records pertains to a specific period need to be subjected to this method then data filtering must be done. Create a Query using Table1 and use 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 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.

You have different ideas? Please share it with me by posting them into the Comments Section.


Share:

No comments:

Post a Comment

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Base Class and Derived Object Variants

Last week we have tried an example as how to pass a Base Class Object, through the Set Property Procedure,  to become part of the Object in...

Labels

Blog Archive

Recent Posts