Data Files samples / HR, payroll and timesheets
Wrenmoor Garden Centre
Staff, contracts, holidays, absence, timesheets, pay runs and payslips for a business with employees.
Queries
The questions this database answers, saved under a name. Open one to see its answer, or its design to see how it is built and change it.
- Who is off, next 7 days
Everyone away today or in the next seven days, for holiday, sickness or other leave, with Off Today marked Yes for those away now. Requests not yet approved are left out. Use it each morning to plan cover.
From Absences, Employees, Departments. Shows Employee, Department, Kind, Starts, Ends, Days, Off Today.
Calculated: Employee is
[First Name] & " " & [Last Name]; Off Today isIIf(DateDiff("d", [Starts], Date()) >= 0 And DateDiff("d", Date(), [Ends]) >= 0, "Yes", "No").Only where
Starts In <= 7andEnds In >= 0andStatus In ("Approved", "Recorded").- Leave requests to decide
Holiday and other leave asked for but not yet approved or declined, by the first day off, with how many days each has waited.
From Absences, Employees, Departments. Shows Number, Employee, Department, Kind, Starts, Ends, Days, Requested On, Days Waiting.
Calculated: Employee is
[First Name] & " " & [Last Name]; Days Waiting isDateDiff("d", [Requested On], Date()).Only where
Status = Requested.- Holidays left
This holiday year's allowance for each person still working for you: the allowance, days carried over, days booked and the days left, by department.
From Holiday Allowances, Employees, Departments. Shows Department, Employee, Holiday Year, Allowance, Carried Over, Days Booked, Days Left.
Calculated: Employee is
[First Name] & " " & [Last Name]; Days Left is[Allowance] + Nz([Carried Over]) - Nz([Days Booked]).Only where
Days Into Year >= 0andDays Left in Year >= 0andLeave Date Is Null.- Holiday taken and to come
Approved holiday this year from the absences themselves: the days already taken and the days still to come for each person, beside Days Booked on their allowance, which they should add up to. Someone with no holiday approved this year is not listed.
From Absences, Holiday Allowances, Employees. Shows Employee, Holiday Year, Days Booked, Days Taken, Days To Come, with totals.
Calculated: Employee is
[First Name] & " " & [Last Name]; Days Taken isIIf(DateDiff("d", [Starts], Date()) > 0, [Days], 0); Days To Come isIIf(DateDiff("d", [Starts], Date()) > 0, 0, [Days]).Only where
Status = ApprovedandDays Into Year >= 0andDays Left in Year >= 0.- Timesheets to approve
Timesheets submitted and not yet approved, the oldest week first, with the days each has waited. Clear it each week, and before the month's pay run.
From Timesheets, Employees, Departments. Shows Number, Employee, Department, Week Commencing, Total Hours, Overtime Hours, Submitted On, Days Waiting.
Calculated: Employee is
[First Name] & " " & [Last Name]; Days Waiting isDateDiff("d", [Submitted On], Date()).Only where
Status = Submitted.- Overtime by department, last month
The shifts, hours and overtime hours worked in each department last calendar month, from the timesheet lines.
From Timesheet Lines, Timesheets, Employees, Departments. Shows Month, Department, Shifts, Hours Worked, Overtime Hours, with totals.
Calculated: Month is
Format([Date], "yyyy-mm").Only where
Months Ago = 1.- Hours and overtime by month
Hours and overtime worked by hourly staff each month, which shows the busy seasons.
From Timesheet Lines. Shows Month, Shifts, Hours Worked, Overtime Hours, with totals.
Calculated: Month is
Format([Date], "yyyy-mm").- Payroll cost by department
For each month and department: how many payslips, gross pay, the employer costs on top, the total cost to the business and the net paid. The department is the one each person is in now.
From Payslips, Pay Runs, Employees, Departments. Shows Month, Department, Payslips, Gross, Employer Costs, Total Cost, Net, with totals.
Calculated: Month is
Format([Pay Date], "yyyy-mm"); Total Cost isRound([Gross] + [Employer Costs], 2).- Pay run totals
Each pay run, the newest first, with its payslips and its gross, deductions, net and employer costs.
From Pay Runs, Payslips. Shows Number, Frequency, Period, Pay Date, Payslips, Gross, Deductions, Net, Employer Costs, with totals.
- Pay to date
For each pay year and person: the payslips, gross pay, deductions, net pay and employer costs so far. The current year's figures are the year to date.
From Payslips, Pay Runs, Pay Years, Employees. Shows Pay Year, Employee No, Employee, Payslips, Gross, Deductions, Net, Employer Costs, with totals.
Calculated: Employee is
[First Name] & " " & [Last Name].- Pay to date by item
The same, item by item: basic pay, overtime and holiday pay, and each deduction, for each person in each pay year.
From Payslip Lines, Payslips, Pay Runs, Pay Years, Employees. Shows Pay Year, Employee No, Employee, Kind, Item, Amount to Date, with totals.
Calculated: Employee is
[First Name] & " " & [Last Name].- Starters and leavers
People who joined or left in the last 12 months, and anyone due to start in the next three months, the newest first.
From Employees, Departments. Shows Employee No, Employee, Department, Job Title, Start Date, Leave Date, Leaving Reason, Change.
Calculated: Employee is
[First Name] & " " & [Last Name]; Change isIIf(Nz([Leave Date], "") = "", IIf(DateDiff("d", Date(), [Start Date]) > 0, "Starting soon", "Joined"), IIf(DateDiff("d", [Start Date], Date()) <= 365, "Joined and left", "Left")).Only where
Days Since Start Between -90 And 365andDays Since Leaving Between 0 And 365.- Absence by person
Absence other than holiday in the last 12 months for each person: sickness spells and days, other days, and the days absent in all, the most first. Days in 12 months is the person's absence rate in days a year.
From Absences, Employees, Departments. Shows Employee, Department, Sickness Spells, Sickness Days, Other Days, Days Absent, with totals.
Calculated: Employee is
[First Name] & " " & [Last Name]; Sickness Spells isIIf([Kind] = "Sickness", 1, 0); Sickness Days isIIf([Kind] = "Sickness", [Days], 0); Other Days isIIf([Kind] = "Sickness", 0, [Days]).Only where
Kind <> "Holiday"andStatus In ("Approved", "Recorded")andDays Ago Between 0 And 365.- Birthdays and anniversaries
Staff with a birthday or a work anniversary this month or next, with the date and the years served.
From Employees, Departments. Shows Employee, Department, Birthday, Work Anniversary, Years.
Calculated: Employee is
[First Name] & " " & [Last Name]; Birthday isIIf((Month([Date of Birth]) - Month(Date()) + 12) Mod 12 <= 1, Format([Date of Birth], "dd") & "/" & Format([Date of Birth], "mm"), ""); Work Anniversary isIIf((Month([Start Date]) - Month(Date()) + 12) Mod 12 <= 1 And Year(Date()) - Year([Start Date]) + IIf(Month([Start Date]) < Month(Date()), 1, 0) >= 1, Format([Start Date], "dd") & "/" & Format([Start Date], "mm"), ""); Years isIIf((Month([Start Date]) - Month(Date()) + 12) Mod 12 <= 1 And Year(Date()) - Year([Start Date]) + IIf(Month([Start Date]) < Month(Date()), 1, 0) >= 1, Year(Date()) - Year([Start Date]) + IIf(Month([Start Date]) < Month(Date()), 1, 0), Null).Only where
Birthday In <= 1andAnniversary In <= 1andYears Served >= 1andDays Served >= 0andDays Served >= 0andLeave Date Is NullandLeave Date Is Null.- Documents to chase
Contracts not yet signed back, right to work checks and other papers not yet done, and checks that must be done again within 60 days, for people still working for you.
From Documents Held, Employees. Shows Employee, Document, Issued On, Completed On, Expires On, To Do.
Calculated: Employee is
[First Name] & " " & [Last Name]; To Do isIIf(Nz([Completed On], "") = "", "Not done yet", "Check again before it expires").Only where
Completed On Is NullandDays to Expiry <= 60andLeave Date Is NullandLeave Date Is Null.- Staff list
Everyone working for you now, by department, with their job, start date, pay type, hours and rate.
From Employees, Departments. Shows Department, Employee No, Employee, Job Title, Start Date, Pay Type, Pay Frequency, Hours per Week, Hourly Rate, Annual Salary, Phone.
Calculated: Employee is
[First Name] & " " & [Last Name].Only where
Leave Date Is NullandDays Served >= 0.- Emergency contacts
Who to call for each person working for you now, by department.
From Employees, Departments. Shows Department, Employee, Phone, Emergency Contact, Emergency Relationship, Emergency Phone.
Calculated: Employee is
[First Name] & " " & [Last Name].Only where
Leave Date Is Null.- Pay history
Every change of job, pay or hours, by person, the newest first.
From Pay Changes, Employees. Shows Employee, Effective From, Reason, Job Title, Pay Type, Hourly Rate, Annual Salary, Hours per Week.
Calculated: Employee is
[First Name] & " " & [Last Name].- Deduction rates in use
The deduction and employer rates that apply today, in payslip order.
From Deduction Rates. Shows Name, Paid By, Percent, Fixed Amount, Threshold, Applies From, Applies To.
Only where
Days in Force >= 0andDays Still to Run >= 0.
Forms
Every table has a form of the same name: Start Here, Business, Departments, Employees, Pay Changes, Documents Held, Holiday Allowances, Absences, Pay Years, Pay Runs, Timesheets, Timesheet Lines, Deduction Rates, Payslips, Payslip Lines, Annual Summaries, Annual Summary Lines. A form shows one record at a time and adds new ones. A field that points at another table is a list of that table's records, and the records that point at this one are listed below it.
Reports
Pages to print or save as a PDF, laid out from a query or a table.
- Staff by department
The staff list on a page for each department, with a head count and the hours.
From the query Staff list, a group for each Department: Employee No, Employee, Job Title, Start Date, Pay Type, Pay Frequency, Hours per Week, with the count of Employee, total of Hours per Week.
- Emergency contact list
Each person's emergency contact, by department: print it for the office wall or the first-aid box.
From the query Emergency contacts, a group for each Department: Employee, Phone, Emergency Contact, Emergency Relationship, Emergency Phone.
- Holiday balances
This year's holiday allowance, days booked and days left for each person, by department.
From the query Holidays left, a group for each Department: Employee, Holiday Year, Allowance, Carried Over, Days Booked, Days Left, with the total of Days Booked, total of Days Left.
- Absence other than holiday, last 12 months
Sickness and other absence in the last 12 months for each person, by department, with totals.
From the query Absence by person, a group for each Department: Employee, Sickness Spells, Sickness Days, Other Days, Days Absent, with the total of Sickness Days, total of Days Absent.
- Monthly payroll cost
Gross pay, employer costs and total cost for each department, month by month.
From the query Payroll cost by department, a group for each Month: Department, Payslips, Gross, Employer Costs, Total Cost, Net, with the total of Gross, total of Employer Costs, total of Total Cost.
- Pay run register
Every pay run with its totals: the record of what was paid and when.
From the query Pay run totals: Number, Frequency, Period, Pay Date, Payslips, Gross, Deductions, Net, Employer Costs, with the total of Gross, total of Net, total of Employer Costs.
- Overtime last month
Hours and overtime for each department last month.
From the query Overtime by department, last month: Month, Department, Shifts, Hours Worked, Overtime Hours, with the total of Hours Worked, total of Overtime Hours.
- Timesheets awaiting approval
Timesheets waiting for a manager, by department.
From the query Timesheets to approve, a group for each Department: Number, Employee, Week Commencing, Total Hours, Overtime Hours, Days Waiting, with the total of Total Hours.
- Year to date pay
Each person's gross, deductions and net for each pay year, with totals for the year.
From the query Pay to date, a group for each Pay Year: Employee No, Employee, Payslips, Gross, Deductions, Net, Employer Costs, with the total of Gross, total of Deductions, total of Net.
- Start here: the everyday tasks, step by step
The guide to this database printed: each everyday task and its steps, from the Start Here table.
From the table Start Here, a group for each Task: Step, What to do.
Print templates
What the business sends out, one for each record, with its own name, address and payment details at the head from the Business table. Print one from its form, or choose which to print under Print templates, or save them as a PDF. The words in braces are filled in from the record.
Payslip
The payslip for one person and one pay run: their details, the month, the pay date and, for someone paid by the hour, the weeks their hours are from, each payment and each deduction with its year-to-date figure, then gross, deductions and net pay, and the year to date. The tax code is left empty for you to fill. The tax, social insurance and pension rates in this sample are invented examples, named "(example rate)". They are not the rules of any country. Before paying anyone, replace them in Deduction Rates with your own country's current rules or your payroll provider's figures.
One for each record of Payslips, on A4 paper.
Payslip: {Pay Run.Period}
Name · Employee number · Payslip · Job · Department · Pay date · Period from · Period to · Paid · Tax code · Pay type · Pay year · Hours worked from · Hours worked to · Hours paid
A line for each record of Payslip Lines: Pay and deductions, Hours, Rate, Payments, Deductions, Year to date
Gross pay · Total deductions · Net pay
Gross pay, year to date · Deductions, year to date · Net pay, year to date
Net pay is paid by bank transfer on {Pay Run.Pay Date} to bank code {Employee.Bank Code}, account {Employee.Account Number}. On top of your pay, {Business.Name} pays {Employer Costs} in employer contributions for this period.
Please check your payslip and tell us within 14 days if anything looks wrong. Keep your payslips: you may need them to show what you earn.
{Business.Document Footer}
Timesheet
A week's timesheet for one person: each day's start, finish, break and hours, the overtime above their contract hours, and lines for the employee and the manager to sign.
One for each record of Timesheets, on A4 paper.
Timesheet {Number}
Name · Employee number · Department · Week commencing · Contract hours · Status · Submitted · Approved by · Approved
A line for each record of Timesheet Lines: Day, Date, Start, Finish, Break, minutes, Hours, Overtime, Note
Hours worked · Within contract hours · Overtime
Overtime is the time worked above the contract hours for the week, and is paid at one and a half times the hourly rate. Days of holiday or sickness are not shown here: they are paid from the absence record.
I confirm that these are the hours I worked in the week shown.
Signed: Employee, Approved by
Employment contract summary
A summary of a person's main terms, to give with the offer letter and sign: the job, start date, pay, hours, holiday, sickness, pension and notice, filled in from their Employees record. The words are a plain example of our own, not legal advice: have them checked for your own country before you use them. It prints for everyone in Employees, leavers too; print it for one person from the Employees form.
One for each record of Employees, on A4 paper.
Summary of terms of employment
First name · Family name · Address · Employee number · City · Postcode · Job · Department · Reports to · Starts on · Pay type · Paid · Hourly rate, if paid by the hour · Annual salary, if salaried · Hours a week · Days a week · Holiday a year, days · Notice, weeks
This summary sets out the main terms on which {Business.Name}, of {Business.Address}, {Business.City} {Business.Postcode} ("the employer"), employs {First Name} {Last Name} ("you"). It goes with your offer letter and the staff handbook. Please read it, sign both copies and give one back to us.
1. Your job. You are employed as {Job Title} in the {Department} department, reporting to {Manager}. Your manager may ask you to do other reasonable work that suits your skills, such as helping another department at a busy time.
2. Where you work. You work at {Business.Address}, {Business.City}. We will not ask you to work anywhere else for more than a day without agreeing it with you first.
3. Start and trial period. Your employment starts on {Start Date}. The first three months are a trial period, during which either of us may end the employment with one week's notice. Your manager will meet you before it ends to talk about how it has gone.
4. Hours. Your normal hours are {Hours per Week} hours a week over {Days per Week} days, on a rota your manager gives you at least two weeks ahead. Any shift longer than six hours has an unpaid break of 30 minutes. If you are paid by the hour, time worked above your weekly hours is overtime: it is agreed with your manager beforehand and paid at one and a half times your hourly rate. If you are salaried, your salary covers reasonable extra hours at busy times.
5. Pay. Your pay is shown above: an hourly rate for each hour worked, or an annual salary paid in twelve equal parts. You are paid {Pay Frequency}, by bank transfer, on the last working day of the month. A salary is paid for that month. Pay by the hour is paid a month behind, from your approved timesheets: each month's pay covers the weeks that ended in the month before. Each payslip shows your pay and everything deducted from it: the deductions the law requires, your pension contributions, and anything else you have agreed to in writing.
6. Holiday. Your holiday is {Holiday Days} days a year, worked out for the days you work each week. The holiday year runs from 1 January to 31 December; if you join or leave during the year you have a share for the part of the year you work. Ask for holiday on a leave request at least two weeks ahead. At the busiest times of year we may ask you to choose other dates. Up to five unused days may be carried into the next year, and holiday you have earned but not taken when you leave is paid with your last pay.
7. Sickness. If you are ill and cannot work, call your manager before your shift starts, or as soon as you can, and say how long you expect to be off. Sick pay is set out in the staff handbook and is never less than the law requires.
8. Pension. We will offer you a place in the workplace pension scheme where the law requires it, and you may ask to join at any time. What you and we pay in is shown on each payslip.
9. Notice. After the trial period, either of us may end your employment by giving {Notice Weeks} weeks' notice in writing, or more where the law requires it. We may pay you for the notice period instead of asking you to work it.
10. Rules and concerns. The staff handbook sets out how we deal with discipline and with any concern you raise. Speak first to your manager; if your concern is about your manager, speak to the office.
11. Changes. We will tell you in writing at least one month ahead of any change to these terms, unless you agree to it sooner.
Signed for {Business.Name}, and by you to show that you have received these terms and agree to them.
Signed: For the employer, Employee, Date
Leave request
The form for asking for leave and recording the manager's decision, with the holiday allowance for the year. It prints for every absence, sickness too; print the one you need from the Absences form.
One for each record of Absences, on A4 paper.
Leave request {Number}
Name · Employee number · Department · Manager · Kind of leave · Asked for on · First day off · Last day off · Working days · Reason
Holiday allowance for the year (holiday only):
Holiday year · Allowance, days · Carried over, days · Booked so far, days
Request. I would like to take the leave shown above. I have checked the rota with my team, and my work will be covered or handed over before I go.
Decision · Decided by · Decided on
Once approved, holiday is taken from the allowance for the year and paid as holiday pay. If a request is declined, your manager will tell you why and help you find other dates.
Signed: Employee, Manager
Annual pay summary
The end-of-year summary for one person and one pay year: each kind of pay and each deduction added up for the year, and the net pay. Give it to each person after the last pay run of the year.
One for each record of Annual Summaries, on A4 paper.
Annual pay summary {Pay Year.Name}
Name · Summary · Employee number · Issued · Address · Pay year from · City · Pay year to · Postcode · Tax code · Payslips in the year · Left on
A line for each record of Annual Summary Lines: Pay and deductions for the year, Pay, Deducted
Total pay · Total deductions · Net pay
This summary shows the pay {Business.Name} paid you in the pay year from {Pay Year.Starts} to {Pay Year.Ends}, and everything deducted from it. In the same year we paid {Employer Costs} in employer contributions on top of your pay.
Please keep it safe: you may be asked for it to show your income, for example for a tax return, a loan or a tenancy. Ask the office if you need another copy.
{Business.Document Footer}
Back to the overview of Wrenmoor Garden Centre