Access 2007 - group for every 2 weeks

  • Thread starter Thread starter M Skabialka
  • Start date Start date
M

M Skabialka

I would like to group and summarize my employee hours report by pay periods.
Pay periods are two weeks and end on a Sunday at midnight. I want to list
the hours they work each day, plus a summary for the pay period. How do I
do that in Sorting and Grouping?
 
If you want to finish weeks on Sunday night, subtract 1 from the date field
to get the weeks correct. Then to group 2 weeks together do an integer
divide. Your sorting and grouping expression might look like:
=DatePart("ww",[DateField]-1)\2
 
Thanks, this gets me the pay periods, but pay periods don't start on any
random Monday - how can I specify the two week groupings based on actual pay
period dates? I also found out pay periods end Saturday night - should this
make a difference to the formula since WeekDay([a sunday date]) = 1?
Thanks,
Mich

Duane Hookom said:
If you want to finish weeks on Sunday night, subtract 1 from the date
field
to get the weeks correct. Then to group 2 weeks together do an integer
divide. Your sorting and grouping expression might look like:
=DatePart("ww",[DateField]-1)\2
--
Duane Hookom
Microsoft Access MVP


M Skabialka said:
I would like to group and summarize my employee hours report by pay
periods.
Pay periods are two weeks and end on a Sunday at midnight. I want to
list
the hours they work each day, plus a summary for the pay period. How do
I
do that in Sorting and Grouping?
Click to expand...
Click to expand...
 
Actually since pay periods may change in the future, I would just create a
table of pay periods with begin and end dates. You can then add this to the
record source of your report and set the criteria under the [Unnamed Date
Field] to
Between PayPeriodStart And PayPeriodEnd
You can include these two fields in the report's fields so they can be used
for grouping in the report.

--
Duane Hookom
Microsoft Access MVP


M Skabialka said:
Thanks, this gets me the pay periods, but pay periods don't start on any
random Monday - how can I specify the two week groupings based on actual pay
period dates? I also found out pay periods end Saturday night - should this
make a difference to the formula since WeekDay([a sunday date]) = 1?
Thanks,
Mich

Duane Hookom said:
If you want to finish weeks on Sunday night, subtract 1 from the date
field
to get the weeks correct. Then to group 2 weeks together do an integer
divide. Your sorting and grouping expression might look like:
=DatePart("ww",[DateField]-1)\2
--
Duane Hookom
Microsoft Access MVP


M Skabialka said:
I would like to group and summarize my employee hours report by pay
periods.
Pay periods are two weeks and end on a Sunday at midnight. I want to
list
the hours they work each day, plus a summary for the pay period. How do
I
do that in Sorting and Grouping?
Click to expand...
Click to expand...
Click to expand...
 
I think I have what I need - found an entry from John Spencer (MVP) from
2003:

How about using an expresssion like:
DateDiff("d",#5/5/2003#,YourDateField)\14 + 1

Thanks,
Mich.

Duane Hookom said:
If you want to finish weeks on Sunday night, subtract 1 from the date
field
to get the weeks correct. Then to group 2 weeks together do an integer
divide. Your sorting and grouping expression might look like:
=DatePart("ww",[DateField]-1)\2
--
Duane Hookom
Microsoft Access MVP


M Skabialka said:
I would like to group and summarize my employee hours report by pay
periods.
Pay periods are two weeks and end on a Sunday at midnight. I want to
list
the hours they work each day, plus a summary for the pay period. How do
I
do that in Sorting and Grouping?
Click to expand...
Click to expand...
 
These are based on Government pay periods. I think it would take an Act of
Congress to change them :-)

Duane Hookom said:
Actually since pay periods may change in the future, I would just create a
table of pay periods with begin and end dates. You can then add this to
the
record source of your report and set the criteria under the [Unnamed Date
Field] to
Between PayPeriodStart And PayPeriodEnd
You can include these two fields in the report's fields so they can be
used
for grouping in the report.

--
Duane Hookom
Microsoft Access MVP


M Skabialka said:
Thanks, this gets me the pay periods, but pay periods don't start on any
random Monday - how can I specify the two week groupings based on actual
pay
period dates? I also found out pay periods end Saturday night - should
this
make a difference to the formula since WeekDay([a sunday date]) = 1?
Thanks,
Mich

Duane Hookom said:
If you want to finish weeks on Sunday night, subtract 1 from the date
field
to get the weeks correct. Then to group 2 weeks together do an integer
divide. Your sorting and grouping expression might look like:
=DatePart("ww",[DateField]-1)\2
--
Duane Hookom
Microsoft Access MVP


:

I would like to group and summarize my employee hours report by pay
periods.
Pay periods are two weeks and end on a Sunday at midnight. I want to
list
the hours they work each day, plus a summary for the pay period. How
do
I
do that in Sorting and Grouping?
Click to expand...
Click to expand...
Click to expand...
 
I would probably prepare for the Act Of Congress by creating the table ;-)
--
Duane Hookom
Microsoft Access MVP


M Skabialka said:
I think I have what I need - found an entry from John Spencer (MVP) from
2003:

How about using an expresssion like:
DateDiff("d",#5/5/2003#,YourDateField)\14 + 1

Thanks,
Mich.

Duane Hookom said:
If you want to finish weeks on Sunday night, subtract 1 from the date
field
to get the weeks correct. Then to group 2 weeks together do an integer
divide. Your sorting and grouping expression might look like:
=DatePart("ww",[DateField]-1)\2
--
Duane Hookom
Microsoft Access MVP


M Skabialka said:
I would like to group and summarize my employee hours report by pay
periods.
Pay periods are two weeks and end on a Sunday at midnight. I want to
list
the hours they work each day, plus a summary for the pay period. How do
I
do that in Sorting and Grouping?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top