Hi, first off I am creating a database to store and create payments to staff.

I have a table called main with the following fields...

ID, staffnumber, surname, forename, pensionband, pensionspercentage, NI1, NI2, NI3, NI4, NI5, NI6, NI7, NI8, NI9, Hostlocation.
This stores all the staff entries into the database.

I then have another table called payments with fields....


claimID, ID, dateofsession, dayofweek, typeofrate, rateofpay, hoursworked, mileageclaim, totalclaim.

This table will store each payment entered for each staff member.

I use a form linked to main and subform linked to payments to enter the details for the revelant table fields.

Now what I need is a table and maybe a form or query to calcualte how much a staff is entitled to per month.

The fields from an excel sheet that I have been given are as follows...
TotalClaim, Expenses, Pensionable Pay, Pension %, Addition Contribution %, Employer Contribution %, Total Pension and Net pay

To calcualte the net pay as follows.

TotalClaim - Expenses = Pensionable Pay
Pension % + Addition Contribution % + Employer Contribution % = Total Pension
Pensionable Pay - Pension % - Additional Contributon % = NET PAY

Now I gather I need a form to enter in Additional Contribution %, this is an option for staff, they can choose to add additional % to their pension every month, so it will either be blank or some staff will choose to add say 1% or 5% extra to their pension each month.

Pension % is taken from the main table, which has a pensionpercentage (linked to pension band, so on form select band A and pension would = 5%).

Employer contribution % is how much we pay towards staff pension, so this amount is 14%.

You take pension % and additional contribution % away from pensionable pay to give you the net pay due to staff.

How is best to go about this?

I think I need a new table called say monthlypay, to store each months payment made to each staff memeber.

I have to other lookup tables called pension which has ID, band and pecentage fields. So these can change later and not effect previous entries. I also have rate table which has ID, typeofwork and typeofrate fields to calcualte the work rates due and store them in payments table.

Anyone got any ideas on best way to do these calculations?

Any more info needed please let me know.
Cheers
Will

This is a prime example where storing certain calculated fields in a table is the correct method. The reason behind this is that a person may wish to change their additional % at any time during their employ. If the pay was a calculated amount then historically the data would be incorrect.

As you have stated there are several individual elements of person pay that make up the gross and net amounts. What you need to decide is what will change historically and what will not. The ones that don't can be calculated in a query, the ones that don't need to be calculated and saved to the payments history table. You will need an unbound form to collect and calculate the results, then a submit button which will add the results to the table. The mathmatics will be the same as you described.

How to design your form:
I suggest you ask HR for a sample payslip and desing your form around this. This way you know you have not missed out any fields and it will be easy to check your results with an existing payslip. Also anyone using it will be instantly familiar with its layout.

David

I would suggest not storing any calculations in your main tables - these can always be obtained from other existing data.

A History table for storing all the details of payments would be a must, as you point out, pension% can change over time & you don't wont it changing history

This is a prime example where storing certain calculated fields in a table is the correct method. The reason behind this is that a person may wish to change their additional % at any time during their employ. If the pay was a calculated amount then historically the data would be incorrect.

As you have stated there are several individual elements of person pay that make up the gross and net amounts. What you need to decide is what will change historically and what will not. The ones that don't can be calculated in a query, the ones that don't need to be calculated and saved to the payments history table. You will need an unbound form to collect and calculate the results, then a submit button which will add the results to the table. The mathmatics will be the same as you described.

How to design your form:
I suggest you ask HR for a sample payslip and desing your form around this. This way you know you have not missed out any fields and it will be easy to check your results with an existing payslip. Also anyone using it will be instantly familiar with its layout.

David

Thanks David, I know calcualted data should not be stored in a table, why I put this I do not know.

I think from what I have been given etc.. that things that will change will be penison % (may go up or down next year), additional contribution % (staff can change whenever they like), employer contribution % (employer can change year on year), gross pay will change month on month, net pay will change month on month, as will expenses, pensionable pay.

I guess I dont need to worry about the payments table as this is storing payments for each staff member and will continue to do so all year long and so on.

The payslip I have in excel form now looks like this:

<img src="http://i27.tinypic.com/2ho8eb4.jpg (http://www.access-programmers.co.uk/forums/<a href="http://tinypic.com" target="_blank"><img src="http://i27.tinypic.com/2ho8eb4.jpg" border="0" alt="Image and video hosting by TinyPic"></a>)" border="0" alt="Image and video hosting by TinyPic">

http://i27.tinypic.com/2ho8eb4.jpg


So would I create a form, to enter every detail on it? including the payment form and main form details? Or do I have a seperate form to calcualate the monthly pay only?

To clarify:
Weekly/monthly salrary details are stored in the history table containing both caculated and non calulated values. Once written to the tablew they effectively become read only as they are retrospective payments. Use current employee settings to calculate the current salary.

David

Having responded to #3 in #5 before viewing #4 I now respond to #4.

Are you using an Excel spreadsheet to collect the data at the moment, as per your attachment? Is it this that you are attempting to automate in Access?

What is the current process at the moment, I might have a few good ideas that I can offer you, but this depends on what you are doing now.

David

To clarify:
Weekly/monthly salrary details are stored in the history table containing both caculated and non calulated values. Once written to the tablew they effectively become read only as they are retrospective payments. Use current employee settings to calculate the current salary.

David

SO ideally how many tables should I have?

I have 2 at the moment, main (includes staff details) and payments (include each payment made to each staff member).

Do I need a third called History?

Also do I need a seperate form for this history table or something else? How many forms should I have? I currently have 1 form which includes a sub form within it.

First of all you will need a table for all your employees which along with their demographic details will store their current personal contribution percentages.

Then you need a payment history table that will store the weekly/monthly payements for each employee.

To support the above tables you need any lookup tables that are required. If you imaging you have a main form / sub form setup. Ther main form will be where you are recording the details for the employees current wage. You will pick an employee, say from a combo box which will have their PK as the bound field which becomes the master linked field. You subform will be based on the payments history table which will contain the employee id as the FK this will become the child linked field of the form. So as you select an employee any payment history they have will appear in the subform.

You then enter details for the current payment in the main form and save to history table, which will requery the subform and be displayed accordingly.

This is it in a nutshell.

David

See if you can read this version. Have added both the zip and the mdb.

David

can you clarify the point of all this?, as you are dealing with pensions

are you trying to calculate tax/income tax and social security benefits also. (I see you have fields for NI etc)

If not, I cannot for the life of me undestand why you are bothering to calcluate pension contributions. And if you are calculating tax, then I really would not bother. (certainly in the UK) Payroll and Tax etc is not to be undertaken lightly, and best left to professionals payroll services people - ie buy a program to do it, or use a bureau.

I could probably knock up a decent payroll program in access in a couple of days, but there are so many regulations surrounding permitted deductions etc, tax codes, national insurance numbers, which are changing at least annually, and which a user cannot afford to get wrong - that it just isnt worth the effort.