Results 1 to 4 of 4
  1. #1
    gicarto is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Madison, Wisconsin
    Posts
    11

    Filtering Previous Wage/Benefits Dates and Values

    This is my first post on this forum. I am trying to write a select query that will compare payroll dates to benefit dates for employees working on government building projects. The database is used to track whether employers are paying their employees the federal prevailing wages for working on government buildings and utilities. Throughout the course of a funded project, an employee must receive a certain amount of money. The problem is, wages and benefits change from time-to-time. The following table is results from the wages and benefits query for an employee who's benefits have changed since the start of the project.

    Payroll_No Wage Date Fringe Date Employee Class Required Pay Hours Overtime Pay Fringe Expired Benefits
    4 10/14/2012 10/14/2012 Employee1 Truck 3 40.44 8.5 FALSE 40.44 17.13 TRUE
    4 10/14/2012 11/18/2012 Employee1 Truck 3 40.44 8.5 FALSE 23.31 0 FALSE
    5 10/21/2012 10/14/2012 Employee1 Truck 3 40.44 10 FALSE 40.44 17.13 TRUE
    5 10/21/2012 11/18/2012 Employee1 Truck 3 40.44 10 FALSE 23.31 0 FALSE
    8 11/11/2012 10/14/2012 Employee1 Truck 3 40.44 10 TRUE 52.1 17.13 TRUE
    8 11/11/2012 10/14/2012 Employee1 Truck 3 40.44 15.5 FALSE 40.44 17.13 TRUE
    8 11/11/2012 11/18/2012 Employee1 Truck 3 40.44 10 TRUE 34.97 0 FALSE
    8 11/11/2012 11/18/2012 Employee1 Truck 3 40.44 15.5 FALSE 23.31 0 FALSE
    9 11/18/2012 10/14/2012 Employee1 Truck 3 40.44 18 FALSE 32.13 17.13 TRUE
    9 11/18/2012 11/18/2012 Employee1 Truck 3 40.44 18 FALSE 15 0 FALSE



    In this situation, the employee's health benefits changed on 11/18/2012 ($0.00) from the original date of 10/14/2012 ($17.13) resulting in 5 duplicate records. I need to write a filter that will remove the five invalid rows in the results set. I want to compare the original benefit date (10/14/2012) to payroll dates that happened before the benefit date changed on 11/18/12 and make sure that all future payroll dates don't compare for the original benefit dates. I wrote a filter that excluded all of the payroll dates that were less than the latest benefit date (11/18/12). I also need the filter to remove the record that shows the first payroll date of 10/14/2012 and the benefit date of 11/18/12 which is invalid. Filtering this last record is where I am stuck. Any help would be appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The Fringe field may show duplicate values but the records are not duplicates because the data is not the same in all fields.

    Without understanding how you arrived at this dataset, it's difficult to offer solution.

    Which of the 10 records displayed are the 5 invalid records?

    What determines the 'first' or 'last' record as first or last - a record ID? Certainly not the WageDate because the date is not unique, even Payroll_No and WageDate combined are not unique. Why do those records need to be removed?

    Do you intend to use this query as the source for a report object?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    gicarto is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Madison, Wisconsin
    Posts
    11
    I am trying to create a report that will make sure employees were paid the right wages and fringes for a given date (end of payroll week). I have two data sets that I am trying to compare: a payroll table and a benefit table. I am including the information for the one employee I am having trouble with.

    Payroll table indicates the payroll dates, employee,
    Payroll No. Payroll Date Benefit ID Employee Classification Hours Wage Overtime
    1 10/14/12 1 Employee 1 Truck Driver 8 25.5 Yes
    2 10/21/12 1 Employee 1 Truck Driver 6 25.5 No
    3 11/11/12 1 Employee 1 Truck Driver 4 25.5 No
    4 11/18/12 2 Employee 1 Truck Driver 5 25.5 Yes

    Then there is a benefit table where one employee has a change in his health benefits.
    Employee ID Benefit ID Benefit Name Benefit Date Reported Benefit Amount Expired Benefit
    1 1 Health Insurance 10/14/12 17.13 Yes
    1 2 Pension 10/14/12 10.00 No
    1 3 Health Insurance 11/18/12 0.00 No

    The query that I am trying to build compares the payroll table with the benefits table. The query sums the different benefits by employee and the number is added to the wage the employee was paid that week. The problem I am running into here is that the employee's health benefit changed on 11/18/12 to 0.00 from the original benefit date of 10/14/12 ($17.13). In the query, I am trying to have the 10/14/12 benefit rate compare to the 10/14/12, 10/21/12 and 11/11/12 payroll dates. Next, I want to compare the 11/18/12 benefit rate with payrolls dated 11/18/12 and later. The results I am getting from the query right now compares all of the benefit dates with all of the payroll dates.

    I have eight records coming back for the given employee when I should only have four. Here are the dates with the invalid results in red:

    10/14/12-10/14/12: the dates match
    10/14/12-11/18/12: benefit date is greater than the payroll date
    10/21/12-10/14/12: payroll date is greater than the benefit date
    10/21/12-11/18/12: Benefit date is greater than the payroll date
    11/11/12-10/14/12: payroll date is greater than the benefit date
    11/11/12-11/18/12: Benefit date is greater than the payroll date
    11/18/12-10/14/12: 11/18/12 is the new benefit date and 10/14/12 is the old benefit date
    11/18/12-11/18/12: the dates match

    I have been able to exclude the records where the benefit date is greater than the payroll date but I can't exclude the situation where the new wage date is compare to the old benefit date.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Is this benefit change mid-payperiod? Shouldn't payroll changes take effect at beginning of pay period? Seems that's the policy of every employer I ever worked for. Identify the pay period that dates fall in and join records on pay period ID.

    I would never try to develop a payroll accounting system. I would buy QuickBooks, which I have used.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-02-2012, 11:09 AM
  2. Automatically show previous dates.
    By mbonning in forum Access
    Replies: 6
    Last Post: 09-12-2011, 12:12 PM
  3. Filtering Dates Error!
    By emilyrogers in forum Forms
    Replies: 3
    Last Post: 02-15-2011, 03:00 AM
  4. Replies: 1
    Last Post: 03-27-2010, 06:13 AM
  5. Filtering Report with between dates
    By patrickmcdiver in forum Reports
    Replies: 3
    Last Post: 02-22-2010, 12:11 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums