Grouping by Pay Period

[Linda Ribbach]

I want to create report that shows how much vacation/sick leave an
employee has taken within a pay period starting with May 5, 2003.


I want to group the data by Pay period. How do I do that?

I have a date field called Date, PdVac, PVSick. and Employee Name.


Thanks In Advance

Linda

 

[Larry]

-----Original Message-----
I want to create report that shows how much vacation/sick leave an
employee has taken within a pay period starting with May 5, 2003.


I want to group the data by Pay period. How do I do that?

I have a date field called Date, PdVac, PVSick. and Employee Name.


Thanks In Advance

Linda

.
create a table containing your payperiods with an ID

number for each. In the table you mention create a field
for this id number. create a relationship between the two
table based on the ID field.
Create a query using both tables.
Create a report using the query
On your report in design select view-sortings and
groupings from the menu and select id as a group
If you want a seperate page for each period the on the
footer properties for the id group you will see force new
page

 

[Linda Ribbach]

Larry,
The data in my table is being entered by staff members. They are
actually using this table as a worklog and a log to keep track of vac
and sick time.

I was hoping to use the date in the table, a field that is an
automatically filled. Can you think of a script that can assign a unique
number for every record within "groups of 14 days" from May 5th on? This
is the beginning of a pay period?

May 5-18 = 1
May 19 - May 30 = 2
June 2 - June 16 = 3

I was thinking that a calendar would have to be set up first, and use
the calendar with all the dates to create the groups 1 and 2 and 3 etc.
I could run a program to assign 1 and 2 and 3 to the full calendar and
then match the calendar dates to my table. Then any dates in my table
would be linked to the complete calendar and given a corresponding pay
period number. This is somewhat similar to what you said but would be
generated by the date not by a manual entry process.

I just don't how to do it. I can't see any other solution. They must be
people out there that have to deal with pay period stuff.

thanks Linda

 

[John Spencer (MVP)]

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

This will give you 1, 2, 3, etc. It will also give you 27, 28, 29 etc in future
years, so that may or may not be a solution for you.