Payroll database design

[lrumd]

I am kinda new with Relationships and subforms, and would appreciate some help.

I am designing a database for payroll in my office. The reason I am doing this is people do not understand the difference between 6:20 (6 hours and 20 minutes) AND 6.2. They add up hours:minutes to hours.decimals and get upset when their paycheck is not what they expected.
The second reason I am doing this, is to keep track of vacation days and overtime not paid (which the employee can later use as time off (1.5 hours off for each overtime hour)). This second point is really secondary at this moment.

The idea is to have the employee enter everyday his/her TimeIn, TimeOut and TimeAtLunch in order to later calculate the number of hours worked. So at some point I will have to create a Form for data input. Not a major issue yet.

I would like to display in a Form (available to me only) the Payroll Date (which I will call PPE, for "Pay Period Ending...&quot in a drop down box, so I could go back and look at information from previous payrolls. In a SUBFORM I would like to list every employee with their calculated values (RegularHoursWk1, RegularHoursWk2, OverTimeWk1, OverTimeWk2) for the PPE selected. I will then manually enter these calculated values into Peachtree Accounting, to print the checks (I do not intend for this Access database App to do the whole thing) We are talking about 12 employees only.

I have thought about the following tables and fields, but any input will be greatly appreciated.



Employees
EmployeeID Auto Number (index)
SSN Number
LastName Text
FirstName Text
MiddleName Text
DateHired D/T
HourlySalary Number
Terminated y/n

HoursWorked
EmployeeID Number (index) --Same as in Employees table
Mon1In D/T (Monday week 1, time in)
Mon1Lunch D/T
Mon1Out D/T
Tue1In
.
.
.
Fri2Out D/T


PayPeriodData
PPE D/T (index), required, no duplicates??
EmployeeID D/T (index), from Employees table
RegularHoursWk1 Number (calculated and stored in this field) (Anything over 40 hours in week 1 goes to Overtime)
RegularHoursWk2 same
OverTimeWk1 Number (anything over 40 hours)
OverTimeWk2 same

And this is when I start getting all confused. And when I try to create my Form and Subform, I can't get it right. I think the problem is in great part due to the Relationships thing. When I look at the Northwind database sample, the relationships show a "1" on one end of the line and a "OO" sign o the other, which I interpret as "1 to many", but my relationships do not show those signs. Am I doing something wrong?

Do I need 2 tables only?

I hope this makes sense. Am I trying to set up something too complicated?

Any help from the experts would be appreciated. Greatly

Luis

 

[TomCologne]

You're right about the one to many symbols. In the "Relationships" window, right-click on the connecting line to bring up the "Edit Relationship/Delete" drop down menu. The "Edit" option lets you determine the relationship between the tables.
Hard to tell what the cause of your problems might be without any details. It's too late today for me to take a closer look at your tbl structure, but are you talking about two (your question) or three (your description)tables?

 

[lrumd]

Thanks, Tom.
I will start by exploring the "edit relationships" procedure you mentioned. Whenever you get a chance, please see if you understand what I am trying to do, and if you think I am setting up things the way I should (number of tables, overall structure, etc)
Thanks,
Luis

 

[TomCologne]

No Problem, Luis:

Some home maintainence issues currently leave little time for Access, but I'll try.
Meanwhile, you might want to check thread700-297977 where I had a link to a MS article about db design posted.
You didn't answer my question about the number of tables, is it two or three?!

You're not alone

Tom

 

[mickeyred]

Irumd,

First of all, you could look at this particular situation as having two big choices to choose from: either you could get something out of it or ... get something out of it (you are smart enough to figure out what I mean).

Secondly, this mezz is based on many assumptions!

On the tblEmployees table you don't need to include the Employee's SSN, HireDate, nor Terminated unless you expect to use this db for other chores. Just 'bout the rest is Ok. FYI: if well designed, you could turn it into a goldmine.

Next: hours worked IS a calculated field. Translation: you do need fields like fldTimeIn, fldLunchOut, fldLunchIn, fldTimeOut in a second tblHoursPerDay table. This, of course, you pretend to calculate everything yourself and have plenty of time to burn. Hint, hint: leave that to Access!

Basically, with info from these two simple tables, Access can calculate just what you need to keep track of vacation days and overtime not paid (which the employee can later use as time off @ 1.5 hours/OT hour worked). BTW, in my company (1,400+ heads working 24/7/365) OT, Vacations and SickDays are second to none! If you need additional ideas, head for hotmail or yahoo and type "mtoledo10@" (sans quotes)!

Best of luck!

Mickeyred!

Far from being an expert and loving it!