1 Reply Latest reply on Sep 28, 2010 2:25 PM by philmodjunk

    Payroll Database Structure Question(s)

    retailmonica

      Title

      Payroll Database Structure Question(s)

      Post

      Hi--

      I'm designing a payroll database and I have some structural questions.

      I am thinking of this as somewhat resembling an invoice database... invoicing for time rather than a product, however there are a couple of riddles that I can't seem to solve.

      FYI 1) I want to have an input screen for a person.  This information is "everything" about the person.
      FYI 2) There should be a way to generate a list of all active employees... which the reason I mention this is because of riddle 1).
      FYI 3) I want to keep my 'invoices' to see what was submitted week after week

      Riddle 1) I want to be able to create an invoice that auto-generates a list of employees.  The reason for this is because I have the same employees every week.  A portal would be cool... but I don't want to have to enter them in like I would in a typical invoice database (i.e., line items).  I just want the current employees to show up... currently they are considered 'active' by the combination of two fields.

      Riddle 2) All employees will be entered via the same input screen, but some employees are salaried, and some are hourly.  I currently have a field that denotes this.  The issue is that I will enter an hourly employee into the system every term (I work at a university) and "archive" old information... thus some employees could have 30 records attributed to them in the initial "People" database (differentiated via a combination of Record - File - and Employee # fields.  I need to grab only the current information.  How can this information be auto updated in my 'invoice' screen?

      Does this make sense?

      If not, please let me know and I'll try to explain better.... :)

      How on earth do I go about structuring this... I'm thinking that some scripting is involved, but I'm getting confused on how many tables are necessary, how they relate and get copied an auto-generate.  !!!  I have looked at invoicing databases... but I still feel lost.

      Sigh.

      Thank in advance.

        • 1. Re: Payroll Database Structure Question(s)
          philmodjunk

          Tables and relationships come first. You need one table where you have one and only one record for each employee. A field in this table can identify employees as "active" and whether they are salaried or hourly.

          A second related table would track at least the work of your hourly employees, though you could also log salaried workers in and out in the same fashion if you need to track the attendance of salaried workers.

          You'd relate the two tables like this:

          Employees::EmployeeID = HoursWorked::EmployeeID

          To see a list of active employees, just perform a find on your Employees table for all "active" employee records. You can even set up layout where you can click a name on this list to switch to a different layout where you can see the employee's current HoursWorked records.