Originally Posted by
ArviLaanemets
You must have a staff registry table like: tblPersons: PersonID, ForeName, LastName, ..., EmployedAt, LeavedAt;
Unless you have same staff for all life of your app, and all your personal remaining with same rank, you also need a table like tblPersonRanks:PresonRankID, PersonID, Rank, ValidFrom, ... (You may consider having possible ranks defined in another table here, and having RankID in this table instead of rank value);
You need a table, where you register all current monthly payments for every person, like tblPersonPayments: PersonPaymentID, PersonID, PaymentTypeID, PaymentSum, IsValid (And probably a table where all possible payment types are registered);
In your payroll table, you will have a row for every payment type valid at date the payment entry was created. Instead persons names and ranks, you'll have PersonID only. Like: tblPayroll: PayrollID, PayrollDate, PersonID, PaymentTypeID, PaymentSum
Every month, you ran a procedure, which:
In case there were any payments for this months registered, either simply deletes them, or asks user must those payments be deleted. When user is asked, and existing entries weren't deleted, the procedure must be aborted;
When the procedure wasn't aborted, it runs an INSERT query, which reads from tblPersonPayments all valid payments for all persons employed currently, adds payroll date, and inserts new payments into tblPayroll.
You need a saved parameter query, which calculates payments for persons for given month. Depending on payment types in tblPayroll, different entries in tblPayroll can be summarized to calculate basic salary, or used to calculate taxes, deductions, etc. down to net salary.