# Thread: Pay Period Calculation (Access 2003)

1. ## 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. ## Re: Pay Period Calculation (Access 2003)

What determines which pay periods fall within a quarter?

3. ## 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. ## 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. ## Re: Pay Period Calculation (Access 2003)

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. ## 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. ## 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. ## 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. ## Re: Pay Period Calculation (Access 2003)

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

10. ## Re: Pay Period Calculation (Access 2003)

Do you really want to group everything by TRACK#?

11. ## Re: Pay Period Calculation (Access 2003)

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

12. ## Re: Pay Period Calculation (Access 2003)

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

13. ## 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. ## 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?

15. ## 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 Last

#### Posting Permissions

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