Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Finding Consecutive Workdays with Query

Introduction

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.


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 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

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is alway...

Labels

Blog Archive

Recent Posts