Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pay Period Calculation (Access 2003)

    Good morning all,

    I am trying to calculate the number of pay periods in a quarter (pay is bi-weekly). Each quarter my have either 6 or 7 pay periods. After determining how many pay periods there are, I also would like to calculate the number of hours worked in a quarter. I have a table with 27 pay periods.

    Ex: Quarter 1 has 6 pay periods. I will need a total of the first 6 pay periods.
    Quarter 1 has 7 pay periods. I will need a total of the first 7 pay periods.

    Quarter 1 has 6 pay periods; Quarter 2 has 7 pay periods. I will need a total of pay periods 7 - 13 for Quarter 2.
    Quarter 1 has 7 pay periods; Quarter 2 has 6 pay periods. I will need a total of pay periods 8 - 13 for Quarter 2.
    Quarter 1 has 7 pay periods; Quarter 2 has 7 pay periods. I will need a total of pay periods 8 - 14 for Quarter 2.

    And so on. I definitely need some guidance on this one. As always, thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pay Period Calculation (Access 2003)

    What determines which pay periods fall within a quarter?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pay Period Calculation (Access 2003)

    Hi Hans

    Pay periods 1 to 6/7 would be quarter 1; pay periods 7/8 to 13/14 would be quarter 2; pay periods 14/15 to 19/20 would be quarter 3; pay periods 20/21 to 26/27 would be quarter 4. This will vary from year to year by where the last pay period of a year ends. Example: Pay Period 1 of 2009 ends January 10, thus giving 6 pay periods in quarter 1 with pay period 6 ending March 21. Quarter 2 has pay period 7 ending April 4 and pay period 13 ending June 27, thus giving 7 pay periods in quarter 2. Hope this helps.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pay Period Calculation (Access 2003)

    Is the following correct?
    1) The first pay period starts on January 1.
    2) A pay period always ends on a Saturday.
    3) The last day of a pay period determines to which quarter it belongs.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pay Period Calculation (Access 2003)

    Answers:

    1. The first pay period does not always start on Jan 1; It carries over from the previous year. This year pay period 1 began December 30, 2007.
    2. The pay period always ends on a Saturday.
    3. Typically, the last day of a pay period does determine to which quarter it belongs. For this year, March 22 ends pay period 6 in quarter 1; April 5 ends pay period 7 in quarter 2; June 28 ends pay period 13 in quarter 2; July 12 ends pay period 14 in quarter 3.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pay Period Calculation (Access 2003)

    Create a query in design view based on your table.
    Select View | Totals or click the Totals button on the toolbar.
    Create the following calculated column:

    Quarter: Format([EndDate], "q")

    where EndDate is the name of the date that contains the end of the pay period.

    Add the EndDate field to the query grid, and the hours worked field.
    Set the Total option for EndDate to Count and that for hours worked to Sum (leave the Total option for Quarter as the default Group By).
    This query should count the pay periods and sum hours worked per quarter.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pay Period Calculation (Access 2003)

    Thanks again Hans,

    This gives me a starting point. I will also need to calculate the number of pay periods in the quarter to determine which of the 27 pay periods I need to put on a report. Ex: If quarter 1 has 6 pay periods I need to select Pay Periods 1 to 6. For quarter 2, I need to determine the number of pay periods in quarter 1 and quarter 2 so I will have a starting point for quarter 2 and an ending point. This will take some VBA and I think I'm up to the task.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pay Period Calculation (Access 2003)

    You could create a new query based on the totals query from my previous reply.
    Add the fields from the totals query to the query grid.
    Let's say that the name of the calculated column that counts the number of pay periods per quarter is CountOfEndDate, and that the name of the query is qryQuarters.
    Create two calculated columns:

    FirstPayPeriod: DSum("CountOfEndDate", "qryQuarters", "Quarter < " & [Quarter]) + 1

    LastPayPeriod: DSum("CountOfEndDate", "qryQuarters", "Quarter <= " & [Quarter])

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pay Period Calculation (Access 2003)

    I am attaching a mini version with the tables and 2 queries. Still baffled. Thanks again.
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pay Period Calculation (Access 2003)

    Do you really want to group everything by TRACK#?

  11. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pay Period Calculation (Access 2003)

    Actually I need the Main table as part of the query also and will group by DIV.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pay Period Calculation (Access 2003)

    So you want to group by DIV instead of by TRACK#?

  13. #13
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pay Period Calculation (Access 2003)

    That is correct. I tried it here and it looks better, but I don't understand the #Error in the 2 fields.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pay Period Calculation (Access 2003)

    Here is a modified version that shows how to group by DIV and by quarter.

    I'm back to my original questions, however. What defines when Pay Period 1 begins?
    Attached Files Attached Files

  15. #15
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pay Period Calculation (Access 2003)

    Generally speaking, Pay Period 1s begin date floats. It could begin in January or in December. For 2008, Pay Period 1s begin date is December 30, 2007. For 2009, Pay Period 1s begin date will be December 28, 2008. In both cases, these are the first days after the last pay period of a year.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •