Automatically insert Date Range into Form

[MelissaKT]

I am attempting to automate the payroll for our auditing department. Currently, I have 2 tables including
Employees: PayDetail:
Employee_No (Key) Employee_No
EmpLastName DateWorked
EmpFirstName RegHrsWorked
EmpAddress OT_Hours
EmpCity Personal_Hrs_Taken
EmpState Holiday_Hrs_Taken
EmpZip Vacation_Hrs_Taken
EmpPhone Sick_Hrs_Taken
DateOfHire Comp_Hrs_Taken
EmpTerminationDate Funeral_Hrs_Taken
EmpInactive Yes/no Comp_Hrs_Earned
Rate PayDetailID (key)

I have a form based on the employee table. The form has a subform based on the PayDetail table. When the employee opens the form, they are only able to see their information. Our pay periods start every other Friday and I would like to automatically include all of the applicable dates into the subform so that the employee / supervisor would only have to plug in their hours. I would also like for the user to have the ability to enter hours for previous pay periods (with all of the appropriate dates automatically being entered when the form is opened). I've tried several different ways to do this but I haven't come up with a good solution. Is there a good way to do this either in vba or in some type of query? I've tried several queries but can't seem to get all of the appropriate dates to appear. Thank you in advance!

 

[LowBrow]

I am not sure what the problem is, whether it is have a Friday through Thursday function or returning a recordset to a subform based on criteria in a main form (or both).

If you clarify, I may be able to help you.

 

[MelissaKT]

Right now, when you open the form, you see the employee information and an empty (datasheet) subform. Right now the user could manually enter the work dates into the subform at the same time that they enter their hours but I want to automatically insert each day within the pay period into the subform so that the employee will only have to enter their hours:
DateWorked RegHrsWorked OT PersonalHrs VacHrs etc
01/09/05
01/10/05
01/11/05
etc.

I only want to insert the current pay period (or a previous one, if the user needs to do that) Does that make more sense? I really appreciate the help!

 

[willir]

MelissaKT

I could suggest code for this, but me being who I am, I am going to recommend a change to your design.

Your employee table is fine, although you may run into problems with the Rate field if it is used for calculations. (Scenario: Johnny Dee gets a pay raise of 10%, then Johnny notices that there were errors with the OT calculations from 6 weeks ago. You have to make sure you use the correct rate to adjust the calculations.)

You should consider a PayPeriod table and use one-to-many relationship for the PayDetail table...

tblPayPeriod
PayPeriodID - primary key
PayStartDate - date
PayEndDate - date
...etc (ChequeDate, etc)

Discussion: This is a template for all affected employees. It allows you to cearly define your pay periods. If you have different pay groups, you would need a PayPeriod for each group.

Now, if you intend to issue cheques, you should also include an employee pay period table to capture the cheque numbers. It can reference the PayPeriod for the dates, but since this is payroll, I would copy some of the data so it reflects details printed on the cheque (a digression from normalization)...

tblEmpPayPeriod
EmpPayPeriodID - primary key
PayPeriodID - foreign key to PayPeriod table
Employee_No - foreign key to your Employee table
PayStartDate - date
PayEndDate - date
ChequeDate - date
CheuqueNo

Then your PayDetail table would change a bit...

IF you are capturing cheque information...

PayDetail
PayDetailID - primary key
EmpPayPeriodID - foreign key to tblEmpPayPeriod
Employee_No - foreign key to employee table
DateWorked - date
HoursWorked
PayType - text, see discussion
Comments - memo to capture any notes

IF you are not capturing pay information, then you can reference the PayPeriod template table...

PayDetail
PayDetailID - primary key
PayPeriodID - foreign key to tblPayPeriod
Employee_No - foreign key to employee table
DateWorked - date
HoursWorked
PayType - text, see discussion
Comments - memo to capture any notes

Discussion: By referencing the Pay Period table, you can easily group your pay periods - useful for statiscal and control purposes.

Note: That rather than list off the pay types you included (regular, OT, Sick, Vacation, etc), I have this as a PayType. This is part of "Normalization" rules you should follow.

With your design...
- To find how many hours you as the employer is responsible for you have to add the fields RegHrsWorked + OT_Hours + Holiday_Hrs_Taken + Vacation_Hrs_Taken... This type of long SQL statement can be quite "painful" when typing it out where needed.
- Maintenance. Suppose you need to differentiate from OT with 1.5X, 2X and 3X. With your design, you have to add new fields, change the forms, and change those herendous SQL statements.

Instead by using a PayType field in the PayDetail table, you only select the pay types and hours worked for each pay type affected. Now if you need to change or add a paytype, it is just a matter of making a record change, not a design change.

tblPayTypes
PayType - primary key, string text
PayRate - numeric

Note: Here, you can assign different pay rates for different pay types...
[tt]
tblPayTypes
PayType PayRate
Regualr 1.0
OT_15 1.5
OT_22 2.0
Personal 0.0
Suspension 0.0
Vacation 1.0
Sick 1.0
Maternity 0.7
[/tt]

When the employee opens the form, they are only able to see their information.

(Aside from asking how the heck are you going to prevent employees from seeing times for other employees)

The employee record would have a subform that depicts the pay periods. Select a pay period to open up a form with the details, or display the details on another subform. The form / subform would use the Employee_NO and PayPeriodID or EmpPayPeriodID to find the detail records.

Make sense??
Richard

 

[MelissaKT]

Richard
Thanks for taking the time to answer. I had seen several of your postings on this subject -- I just wasn't sure how to apply it to my situation. I have absolutely no problem changing my design tomorrow (back to work!). I will probably have more questions on this - I hope that is okay?

As for the form opening to show only the current employees info: the on open event of my form has a filter applied - it filters based on the current user. I realize this wouldn't work in a huge company but it works okay for my company (county government)
Melissa