Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / July 2005

Tip: Looking for answers? Try searching our database.

Table design

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ernst Guckel - 16 Jul 2005 00:19 GMT
Hey,

 I have a payroll database in design.  I have a few tables setup already
but am having a bit of trouble determining the design of the main 'Payroll'
table.  

I have an employee table and a payroll table.  The employee table contains
all employee data, IE: Payrate File# DOB , etc.  The payroll table contains
each payroll run's data... IE: Payroll date, hours, etc.  The delema is in
the payroll table.  There are multiple pay types to be paid.  Regular,
Overtime, Vacation, Sick, etc.  There are two possibilities that I can come
up with.

1.  Have the payroll table contain all of these fields in each record.  But
each record will have empty data fields because each employee does not
recieve ALL pay typers each week...

2.  To put a PayTypeID field and an Hours field that contains the associated
paytype in each record.  More records but less empty fields...  The downside
to this one is I'm not quite sure how to make data entry on forms seemless
with this approach.

eother way... I would prefer to setup the database correctly... Anyone have
advice on which way to go?

Ernst.
Allen Browne - 16 Jul 2005 03:30 GMT
One approach would be to have a main table to store the fact that a payment
was made to an employee on a date, and a related table for each line item in
the payment. The 'overtime' would be a separate line item.

tblPay
   PayID    AutoNumber
   PayDate    Date/Time (date of the payment)
   EmployeeID    foreign key to tblEmployee.EmployeeID

tblPayDetail:
   PayID            foreign key to tblPay.PayID
   PayTypeID    lookup values 'Regular', 'Overtime', ...
   Hours            number (Double)
   PayRate       Currency (dollars per hour)
You may also need an EndDate or date range in tblPayDetail, e.g. if you need
to pay ahead for 'Vacation' pay.

Then it is a matter of programmatically looping through all the timesheet
records that have not already been paid, up to the PayDate, creating records
in these 2 tables, and flagging each timesheet entry as paid. This process
would typically need a batch number, so you can undo the last batch, fix the
timesheets, and have another shot at it if necessary.

Your interface should lock the timesheet entries that have already been
'paid' so they can't be modified. If a correction is needed after the pays
have been administered, it should be by way of a reversal in the next pay
rather than allowing edits of the timesheets. (There will be some extra code
to get this right, e.g. if the timesheets recorded that the guy worked 42
hour week and so you paid him 40 hours regular + 2 hours overtime, and then
it was discovered that a timesheet was duplicated and he worked only 37
hours, you need 2 reversal entries: one removing 2 hours overtime, and
another removing 3 hours regular.)

Hope that at least takes you in a useful direction.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hey,
>
[quoted text clipped - 29 lines]
>
> Ernst
Ernst Guckel - 16 Jul 2005 13:39 GMT
Thanks for the info.  This approach is very helpful but I am concerned about
the data entry aspect of each weeks payroll... Unfortunatley I need to create
a spreadsheet like data entry form with employees down the left and ALL
available hour types going along the top...  Just not sure how to set this up
being that the table design is setup with a paytype as a seperate record
line...

Ernst.

> tblPay
>     PayID    AutoNumber
[quoted text clipped - 60 lines]
> >
> > Ernst
Allen Browne - 16 Jul 2005 15:09 GMT
I don't think I would approach the table design like that.

"All available hour types across the top" means what? That it is a graphical
layout where if the person works from 9am to 5pm, you draw a line from 9am
to 5pm? Somehow I can't imagine that kind of structure coping adequately
with all the possible scenarios that could arise, e.g. where someone has to
leave at 3:25pm for some emergency, and then returns at 4:40 to resume the
last part of the day, or where someone works past midnight into the next
day.

Even if it is possible to create an interface that works as you describe,
I'm not sure that should dictate the data structure of the tables.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thanks for the info.  This approach is very helpful but I am concerned
> about
[quoted text clipped - 81 lines]
>> > have
>> > advice on which way to go?
Ernst Guckel - 16 Jul 2005 15:56 GMT
No.  Not like that... Each week we punch in TOTAL hours in the appropriate
catagories:

Name   PayRate   Hours   OT   Vacation   Sick

John      8.00        40     2.5      0.00      0.00

This is the data entry aspect of what we are looking for.  I understand that
the table design is first.  I know how it is supposed to be done but should
the Customer's request for form interaction play some part as well?  I just
don't see how I can make the data entry form work with your recomended table
design.  Maybe I am just missing something...

I see your design recomendation as the right way to go jus tstuck on how to
implement such a design in the user interface aspect of the project.

Ernst.

> I don't think I would approach the table design like that.
>
[quoted text clipped - 94 lines]
> >> > have
> >> > advice on which way to go?
Craig Alexander Morrison - 16 Jul 2005 18:38 GMT
Ernst the solution, well one solution that I have used is to create a table
or tables to accept the Data Entry in a simple format.

Then following the completion of the data entry the screen has a update
button or you tie it into the close function. This function would process
the data in the temporary table(s) via a series of queries to turn the
flat-wide data in the entry table(s) to the correctly structured deep-thin
data in the database along the lines already discussed.

You would need to ensure that the data entry screen had the necessary rules
in the AfterUpdate of fields to ensure that correct data for that field is
entered to avoid/reduce issues arising when the data is absorbed into the
relational database. These temporary tables maybe best implemented as a
separate MDB on the network to keep them from being confused as part of the
central relational database.

This approach is not a trivial undertaking but it can be a very effective
solution.

Signature

Slainte

Craig Alexander Morrison

> No.  Not like that... Each week we punch in TOTAL hours in the appropriate
> catagories:
[quoted text clipped - 137 lines]
>> >> > have
>> >> > advice on which way to go?
Ernst Guckel - 16 Jul 2005 16:38 GMT
Come to find out 'Batch Payroll' Is what I am trying to do.  Didn't know thee
was a term for it :)

Ernst.

> I don't think I would approach the table design like that.
>
[quoted text clipped - 94 lines]
> >> > have
> >> > advice on which way to go?
Allen Browne - 17 Jul 2005 06:16 GMT
Does Craig's suggestion help?

That approach makes sense if you are forced to go that way.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Come to find out 'Batch Payroll' Is what I am trying to do.  Didn't know
> thee
[quoted text clipped - 120 lines]
>> >> > have
>> >> > advice on which way to go?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.