Pay Period Calculation (Access 2003)

Topic

New Reply

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.

Reply | Quote

Replies

What determines which pay periods fall within a quarter?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Do you really want to group everything by TRACK#?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

So then the question becomes, what is the last pay period of a year? If the first pay period of 2009 starts in December 2008, could the last period of a year ever end in January of the next year?

Reply | Quote

The last pay period of a year will always end in December of the current year.

Reply | Quote

Ok, thanks. I think you’ll need some VBA, I’ll try to come up with something later today.

Reply | Quote

I was thinking along these lines. Calculate # of weeks from beginning of year to beginning of current quarter. Calculate # of weeks in current quarter. Get the difference and that should give a starting point and and ending point.

Reply | Quote

Sorry, still trying to understand. Is the following table of the first pay period for the years 2001 – 2012 correct, i.e. the first pay period for 2005 ended on January 1, 2005?

Year	Start	End
2001	Sunday, 24 December 24, 2000	Saturday, 6 January 6, 2001
2002	Sunday, 23 December 23, 2001	Saturday, 5 January 5, 2002
2003	Sunday, 22 December 22, 2002	Saturday, 4 January 4, 2003
2004	Sunday, 21 December 21, 2003	Saturday, 3 January 3, 2004
2005	Sunday, 19 December 19, 2004	Saturday, 1 January 1, 2005
2006	Sunday, 1 January 1, 2006	Saturday, 14 January 14, 2006
2007	Sunday, 31 December 31, 2006	Saturday, 13 January 13, 2007
2008	Sunday, 30 December 30, 2007	Saturday, 12 January 12, 2008
2009	Sunday, 28 December 28, 2008	Saturday, 10 January 10, 2009
2010	Sunday, 27 December 27, 2009	Saturday, 9 January 9, 2010
2011	Sunday, 26 December 26, 2010	Saturday, 8 January 8, 2011
2012	Sunday, 25 December 25, 2011	Saturday, 7 January 7, 2012

Reply | Quote

I’m out of the office now and will check back (EDT) in the morning. But this does look close.

Reply | Quote

Hans,

Your table is correct.

Reply | Quote

In the end, I think it’s easiest to create a fixed table of pay periods, since there is no easy algorithm to determine them.
I created two tables – one listing all pay periods for 2001 – 2015, and another one listing the first and last pay period for each quarter and the number of pay periods – in Excel, then imported them into Access. You can use these tables in queries or retrieve information from them using DLookup etc.

The attached zip file contains the database with the imported tables, and the workbook (in case you’re interested to see how I created the tables).

Reply | Quote

Thanks Hans for all your time and effort. It’s really appreciated.

Reply | Quote