Calculation based on week and pay period (1 Viewer)

tjnichols · Apr 18, 2012

Hello. I am using Access 2007. I am creating a time tracking database and I want to create a calculation based on when the week ends. It should give the total number of hours worked within a week and refresh as records are entered. Our week starts on Monday and ends on Sunday. Well this is how I see it working but I am open to suggestions!

Thanks!

jzwp22 · Apr 18, 2012

It might be easier to just identify the week number in which the dates occur, you can then group the records by weeknumber & do your sum that way. You can use the datepart() function to determine the week number

datepart("ww", yourdatefield,2)

The 2 in the above tells the function that the week starts on Monday.

The only thing about using the week number is that you will have to be careful when you go from one year to the next since the week numbers will repeat. In other words, you will have to use the year and the week number to group by. You can use the Year() function to extract the year from a date

tjnichols · Apr 18, 2012

So Access will understand that, for instance, this week will end on 4/22? Can you provide an example of the code I will use (if any)? Thanks!!

jzwp22 · Apr 18, 2012

I've attached an example database that illustrates 2 techniques you might use. As mentioned earlier, you can use the year Week number to get your sum; look at the query: qrySumByYearAndWeekNo.

Alternatively, you could determine the week ending date for each work record. You would do that in a query (see query: qryDetermineWeekEndingDate). Then you would use that query in a new query to get the sum by the week ending date (see query: qrSumByWeekEndingDate)

tjnichols · Apr 18, 2012

This is extremely cool!

jzwp22 · Apr 18, 2012

You're welcome

tjnichols · Apr 18, 2012

Ok This is what I have for the Pay Period ending. They are different as we are paid bi-monthly. I have gotten several errors though. I don't do well (AT ALL) writing code so your help is immensely appreciated!

PeriodEnding: DateDiff("m",[DateWorked],*2 + IIf(Day([DateWorked]) > 15, -1, 0) + IIf ([DateWorked]) > 15, 1, 0

jzwp22 · Apr 19, 2012

For bi-monthly periods, I assume that the pay periods end on the 15th and the last day of the month. The difficulty comes in with the last day of the month since it is not a set value (28, 29, 30, 31). To handle this, you basically have to determine the first of the next month and subtract 1. I've added two additional queries to the attached database, one to create the period ending date and the other to do the summing.

tjnichols · Apr 19, 2012

That's perfect! Thank you!!!

jzwp22 · Apr 19, 2012

You're welcome

Calculation based on week and pay period (1 Viewer)

tjnichols

Registered User.

jzwp22

Access Hobbyist

tjnichols

Registered User.

jzwp22

Access Hobbyist

Attachments

tjnichols

Registered User.

jzwp22

Access Hobbyist

tjnichols

Registered User.

jzwp22

Access Hobbyist

Attachments

tjnichols

Registered User.

jzwp22

Access Hobbyist

Similar threads

Users who are viewing this thread