Replies

Thank you Hans. Works great.

Thanks for the reply Hans. I’ll pass this along.

Thanks, that helps somewhat.

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

Hans,

Your table is correct.

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

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.

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

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.

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

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

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

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.

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.

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.