Number of paychecks within the year

[Tara]

I'm working on a database to help track information for our agency's
cafeteria plan. For employees who were here before the start of the year, I
can use a query that divides their total desired yearly witholding by 26
(number of pays per year) in order to calculate their bi-weekly witholding.
The problem is what to do with those employess who start after the beginning
of the year. In this case, they would not receive 26 pays, so I can't
calculate their witholding based on that number. Is there a way to get
access to calculate the number of bi-weekly pays left in the year so that I
could use that number for those types employees instead?

Any help is greatly appreciated!

 

[Jerry Whittle]

Welllll. First off you can't always divide by 26 for every year. Depending on
your pay date, there can be the occasional 27 bi-weekly paydays in a year!

So either way we need to know the actual paydays to answer your question.
For example on which day was the first payday of 2008?

 

[Tara]

We have decided not to take that possible 27th paycheck into consideration.
It will simply be a week where they have no witholding.

Our first payday this year was Jan. 4th.

Thanks for looking at this for me!

 

[Jerry Whittle]

Here's one way to do it. You'll be prompted for the last payday of the year.

PARAMETERS [Enter Last Payday For Year] DateTime;
SELECT Int(([Enter Last Payday For Year]-[ANALYZED])/14) AS PaydaysEOY,
YourTable.*
FROM [YourTable] ;

A couple of things to watch out for. The Int function truncates any decimal
places. So they could have worked half a payday but not get credit. Is this
what you want?

Also this way will count the rare 27th payday. This could be a problem in
either 2010 as it will have 27 of them OR 2009 if you move the payday from
Jan 1, 2010 to Dec 31, 2009.

OH! That brings up a point. If you would move the Jan 1, 2010 payday up to
Dec 31, 2009, you'll have to decide on which date to use for the prompt above
and remember it for the next year.

 

[Tara]

Thanks so much for the help Jerry! I'm getting ready to leave for the day,
but I'll try your suggestion first thing tomorrow.

Here's one way to do it. You'll be prompted for the last payday of the year.

PARAMETERS [Enter Last Payday For Year] DateTime;
SELECT Int(([Enter Last Payday For Year]-[ANALYZED])/14) AS PaydaysEOY,
YourTable.*
FROM [YourTable] ;

A couple of things to watch out for. The Int function truncates any decimal
places. So they could have worked half a payday but not get credit. Is this
what you want?

Also this way will count the rare 27th payday. This could be a problem in
either 2010 as it will have 27 of them OR 2009 if you move the payday from
Jan 1, 2010 to Dec 31, 2009.

OH! That brings up a point. If you would move the Jan 1, 2010 payday up to
Dec 31, 2009, you'll have to decide on which date to use for the prompt above
and remember it for the next year.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

We have decided not to take that possible 27th paycheck into consideration.
It will simply be a week where they have no witholding.

Our first payday this year was Jan. 4th.

Thanks for looking at this for me!

 

[Klatuu]

It actually gets more complicated than Jerry's question. If 1/4/2008 was
your first pay day for 2008, then one could extrapolate 12/21/2007 as the
previous payday. So, with no reliable way to determine programmatically what
the first payday of a year is, you might be better off to create a payday
table. Then you could use a DCount function to determine the number of
paydays for a person hired within the year.