Forms and Subforms

[hhh]

Hi, I'm really getting stuck on creating a form. I've done it before, but this is a step up from what I have done before. This is the relationships I've created. I'm trying to get an entry for payroll. I was thinking that I should have a separate payperiods form (which I can do), then have another form which I can select the relevant pay period, then each member of staff in turn. Then add their hours, any deductions and calculate their net pay. I tried a form with the 'payperiods', then a subform with 'staff', but came to two problems - first, every entry had every member of staff on it, and second, I couldn't work out how to get any further. Hope I am making sense and someone can enlighten me! :banghead:

Thanks very much

 

[informer]

Hi hhh

Your physical data model seems at first sight correct !

But if there is maximum 1 adjustment per tuple (dateID/staffId), merge staffPay and payAdjustments tables.

In opposite case, I propose you to change a little bit your scheme as follows:
In staffPay table

1. Create a PKT = autonumber field named IdStaffPay
2. Create a PKF = unique index on tuple (dateID/staffId)

Here for more about Primary Key technical & functional

in payAdjustments table

1. Remove staffID
2. Remove dateID
3. Add IdStaffPay

After these changes, we will try to solve your problems

 

[plog]

Actually, your relationship is incorrect. The biggest issue I see is a circular path. There should only be 1 way to get from 1 table to another. You have 2 paths from payadjustments to payperiods--directly and then indirectly via staff & staffpay. Only one of those paths should be formalized.

Also, I really think the staffpay and payadjustment data need to be in the same table. Essentially you are creating a table to determine a person's net pay, correct? If so, you should have all your line items that make up that net pay in one table so you can just add them together.

jobtitles also seems incorrect. Can't people change jobs? Get a raise? What happens then? You have no way of capturing that data historically. I don't know if you need that, but there's no way to look a year back and say Joe Smith made $15/hour as an assistant, but now makes $25\hour as a full associate.

Lastly, payperiods probably has to many fields. You don't store data that can be calculated. My guess is that with payperiodstart and payperiodend you can cacluate the payyear and dateofpay and possible payperiodnumber values. If so, then you would instead calculate those fields values instead of storing them.

 

[informer]

As Plog rightly notes, calculated field don't respect the Normal Forms

About merging between staffpay and payadjustment tables, it depends on the number of adjustments possible.

1. Only one adjustment per tuple (dateID/staffId) -> merge
2. More than one per tuple (dateID/staffId) -> modify as indicated previously