Data Files samples / Clinic
Cedar Lane Clinic
Patients, appointments, visit notes, invoices and recalls for a small private clinic.
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.
- Next 7 days
For each day of the coming week and each practitioner, how many appointments are booked and the hours they fill, with the first and last start times. Reception uses it to see the shape of the week at a glance.
From Appointments, Practitioners, Services. Shows Day, Practitioner, Appointments, Booked Hours, First Start, Last Start, with totals.
Calculated: Day is
Format([Start], "yyyy-mm-dd"); Booked Hours is[Minutes] / 60; First Start isMid([Start], 12, 5); Last Start isMid([Start], 12, 5).Only where
Status = BookedandDays Ahead Between 0 And 6.- Day sheet
Every booked appointment for the next seven days, by day and practitioner, with the patient's number, phone, who pays and any practical notes. It is what each practitioner and the desk work from each morning.
From Appointments, Patients, Practitioners, Services, Insurers. Shows Sheet, Time, Patient, Patient No, Phone, Service, Minutes, Paid By, Patient Notes.
Calculated: Sheet is
Format([Start], "yyyy-mm-dd") & " " & [Known As]; Time isMid([Start], 12, 5); Patient is[Patients].[First Name] & " " & [Patients].[Last Name].Only where
Status = BookedandDays Ahead Between 0 And 6.- Diary next 14 days
How full each practitioner's diary is for the next two weeks: appointments, hours booked, hours open and the percentage full. Use it to see who can take more patients or where to offer a cancellation slot.
From Appointments, Practitioners, Services. Shows Practitioner, Role, Appointments, Booked Hours, Open Hours, Percent Full, with totals.
Calculated: Booked Hours is
[Minutes] / 60; Open Hours is[Hours per Week] * 2; Percent Full isRound([Minutes] * 100 / ([Hours per Week] * 120), 2).Only where
Status = BookedandDays Ahead Between 0 And 13.- No-shows and late cancellations
Appointments in the last 90 days the patient did not attend, or cancelled the day before or on the day, with the phone number. Use it to decide on fees and to talk to patients who miss often.
From Appointments, Patients, Practitioners, Services. Shows Start, Patient, Phone, Practitioner, Service, Status, Cancelled On, Days Notice.
Calculated: Patient is
[Patients].[First Name] & " " & [Patients].[Last Name]; Days Notice isDateDiff("d", [Cancelled On], Format([Start], "yyyy-mm-dd")).Only where
Status = No-showandStatus = CancelledandDays Notice <= 1andDays Ago Between 0 And 90andDays Ago Between 0 And 90.- Unpaid invoices
Every invoice not yet paid in full, with who owes it, what has been paid and how many days overdue it is. A minus figure means it is not due yet.
From Invoices, Appointments, Patients, Insurers. Shows Invoice, Owed By, Bill To, Patient, For, Invoice Date, Due Date, Amount, Paid So Far, Balance, Days Overdue.
Calculated: Owed By is
IIf([Bill To] = "Insurer", [Insurers].[Name], [Patients].[First Name] & " " & [Patients].[Last Name]); Patient is[Patients].[First Name] & " " & [Patients].[Last Name]; Balance isRound([Amount] - Nz([Amount Paid]), 2); Days Overdue isDateDiff("d", [Due Date], Date()).Only where
Paid On Is Null.- Money owed
The unpaid balance added up for each insurer and each patient, with the number of invoices and the oldest due date. Start here when chasing payments.
From Invoices, Appointments, Patients, Insurers. Shows Owed By, Bill To, Invoices, Balance, Oldest Due, with totals.
Calculated: Owed By is
IIf([Bill To] = "Insurer", [Insurers].[Name], [Patients].[First Name] & " " & [Patients].[Last Name]); Balance isRound([Amount] - Nz([Amount Paid]), 2).Only where
Paid On Is Null.- Takings by month
Money received each month, split into what came from patients and what came from insurers.
From Payments. Shows Month, From Patients, From Insurers, Takings, with totals.
Calculated: Month is
Format([Paid On], "yyyy-mm"); From Patients isRound(IIf([Method] = "Insurer", 0, [Amount]), 2); From Insurers isRound(IIf([Method] = "Insurer", [Amount], 0), 2).- Takings by practitioner
Money received in the last twelve months for each practitioner's appointments, and how many payments it came in.
From Payments, Invoices, Appointments, Practitioners. Shows Practitioner, Role, Payments, Takings, with totals.
Only where
Days Ago Between 0 And 365.- Fees by service
For each service, how many visits were invoiced in the last twelve months and the total charged. It shows which services earn the most.
From Invoices, Appointments, Services. Shows Service, Kind, Visits, Invoiced, with totals.
Only where
For = VisitandDays Ago Between 0 And 365.- Busy days
Appointments attended or missed in the last year by day of the week, with the hours they took. Use it to plan staff hours.
From Appointments, Services. Shows Day No, Day, Appointments, Hours Booked, with totals.
Calculated: Day No is
DateDiff("d", #2024-01-01#, Format([Start], "yyyy-mm-dd")) Mod 7 + 1; Day isMid("MonTueWedThuFriSatSun", (DateDiff("d", #2024-01-01#, Format([Start], "yyyy-mm-dd")) Mod 7) * 3 + 1, 3); Hours Booked is[Minutes] / 60.Only where
Status In ("Attended", "No-show")andDays Ago Between 1 And 365.- Busy hours
The same appointments by the hour they started, which shows the busy mornings and late afternoons.
From Appointments, Services. Shows Hour, Appointments, Hours Booked, with totals.
Calculated: Hour is
Mid([Start], 12, 2) & ":00"; Hours Booked is[Minutes] / 60.Only where
Status In ("Attended", "No-show")andDays Ago Between 1 And 365.- Recalls due
Open recalls falling due in the next 30 days, with contact details and whether a letter has gone out or the patient has agreed to letters at all.
From Recalls, Patients, Services. Shows Due Date, Days Left, Patient, Phone, E-mail, Recall For, Reason, Letters Allowed, Sent On.
Calculated: Days Left is
DateDiff("d", Date(), [Due Date]); Patient is[Patients].[First Name] & " " & [Patients].[Last Name].Only where
Days Left Between 0 And 30andBooked On Is Null.- Recall letters to send
Recalls due in the next 30 days for patients who agree to letters and have not been sent one yet, with their address. It feeds the recall letters report.
From Recalls, Patients, Services. Shows Recall For, Patient, Address, City, Postcode, Due Date, Days Left.
Calculated: Patient is
[Patients].[First Name] & " " & [Patients].[Last Name]; Days Left isDateDiff("d", Date(), [Due Date]).Only where
Days Left Between 0 And 30andReminders = YesandSent On Is NullandBooked On Is Null.- New patients by month
How many patients registered each month over the last year, and how many of them are insured.
From Patients, Insurers. Shows Month, New Patients, Insured, with totals.
Calculated: Month is
Format([Registered], "yyyy-mm"); Insured isIIf([Kind] = "Insurer", 1, 0).Only where
Days Ago Between 0 And 365.- Not seen for six months
Patients whose last attended appointment was more than six months ago, with the date of that visit. Use it for a "we have not seen you for a while" letter or to tidy the list.
From Patients, Appointments. Shows Patient, Patient No, Phone, Last Visit, Days Since, with totals.
Calculated: Patient is
[Patients].[First Name] & " " & [Patients].[Last Name]; Last Visit isFormat([Start], "yyyy-mm-dd"); Days Since isDateDiff("d", Format([Start], "yyyy-mm-dd"), Date()).Only where
Days Since > 180andStatus = Attended.- Patient list
All patients by family name with their date of birth, contact details, insurer and policy reference.
From Patients, Insurers. Shows Last Name, First Name, Patient No, Date of Birth, Phone, E-mail, Insurer, Policy Reference, Reminders, Registered.
- Visit history
Every visit note by patient, newest first, with the practitioner, the service, the reason and the plan.
From Visit Notes, Appointments, Patients, Practitioners, Services. Shows Patient, Patient No, Seen, Practitioner, Service, Reason, Plan.
Calculated: Patient is
[Patients].[First Name] & " " & [Patients].[Last Name]; Seen isFormat([Start], "yyyy-mm-dd").
Forms
Every table has a form of the same name: Start Here, Business, Insurers, Patients, Practitioners, Services, Appointments, Visit Notes, Invoices, Payments, Recalls. 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.
- Day sheets for the week ahead
A page for each practitioner and day of the week ahead, listing each appointment's time, patient, phone, service and who pays, with the number of patients and minutes booked. Printed each morning for the practitioner and the desk.
From the query Day sheet, a group for each Sheet: Time, Patient, Patient No, Phone, Service, Minutes, Paid By, with the count of Patient, total of Minutes.
- Invoices due
Unpaid invoices grouped by who owes them, each insurer and each patient, with the balance and days overdue and a total for each. For whoever chases payments.
From the query Unpaid invoices, a group for each Owed By: Invoice, Patient, For, Invoice Date, Due Date, Days Overdue, Balance, with the total of Balance.
- Takings by month
A month by month list of money received from patients and from insurers, with totals. For the owners and the accountant.
From the query Takings by month: Month, From Patients, From Insurers, Takings, with the total of From Patients, total of From Insurers, total of Takings.
- Takings by practitioner, last 12 months
Money received in the last twelve months for each practitioner, with a total. Useful when practitioners are paid a share of their takings.
From the query Takings by practitioner: Practitioner, Role, Payments, Takings, with the total of Takings.
- Recall letters to send
The names and addresses of patients to send a recall letter to, grouped by what they are recalled for. Print it, or use it for a mail merge.
From the query Recall letters to send, a group for each Recall For: Patient, Address, City, Postcode, Due Date, with the count of Patient.
- Patient list
Patients grouped by insurer, with patient number, date of birth, phone and policy reference, and a count for each insurer.
From the query Patient list, a group for each Insurer: Last Name, First Name, Patient No, Date of Birth, Phone, Policy Reference, with the count of Patient No.
- 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.
Invoice
The invoice for a visit, a missed appointment or a late cancellation, made out to the insurer with the patient's policy reference, or to the patient. It shows the visit, any payments received, and the balance due, and says that medical care is exempt from VAT. Numbered INV-2026-0042.
One for each record of Invoices, on A4 paper.
Invoice {Number}
Invoice to · Invoice number · Address · Date · Town · Payment due · Postcode · Charge for
Patient · Service · Patient number · Appointment · Date of birth · Seen by · Policy reference · Minutes
Payments received against this invoice:
A line for each record of Payments: Receipt, Paid on, Method, Reference, Received
Fee · VAT · Total · Paid · Balance due
Medical care given at the clinic is exempt from VAT, so no VAT is charged. {Business.Payment Terms} Please pay by card at the desk, or by transfer to {Business.Bank Details}, quoting {Number}.
{Business.Document Footer}
Payment receipt
Given to a patient who pays at the desk, or sent when a transfer arrives: who paid, how much, for which invoice and visit. Numbered RCP-2026-0042.
One for each record of Payments, on A5 paper.
Receipt {Number}
Received from · Date · For invoice · Paid by · Patient · Reference · Service · Appointment
Amount received
Invoice total · Paid to date
Thank you. Medical care is exempt from VAT, so this receipt shows none.
{Business.Document Footer}
Appointment letter
Sent when an appointment is booked: the date and time, who it is with, where to come, who pays, and how to cancel or move it. Every appointment can print one, past ones too, so print it from the Appointments form for the one on screen.
One for each record of Appointments, on A4 paper.
Your appointment
To · Patient number · Address · Booked on · Town · Postcode
Dear {Patient.First Name},
Thank you for booking with us. This letter confirms your appointment.
Date and time · Appointment for · With · Length in minutes · Paid by · Policy reference
Where: {Business.Name}, {Business.Address}, {Business.City} {Business.Postcode}. Please come to reception five minutes before your time. The front door has step-free access and every treatment room is on the ground floor.
If your insurer is paying, please check that your policy covers this appointment and bring your policy reference with you. If you pay for yourself, the fee of {Service.Price} is paid at the desk on the day, by card or cash.
If you need to cancel or move the appointment, please ring us on {Business.Phone} or write to {Business.E-mail} at least 24 hours before. We may charge half the fee for an appointment that is missed, or cancelled with less notice, because we cannot offer the time to someone else.
We look forward to seeing you.
Yours sincerely,
Reception, {Business.Name}
Recall letter
A short, friendly letter asking a patient back for a check that is due, with how to book. Print it for the patients in the Recall letters to send query, from the Recalls form. Have the wording checked against the rules where you work.
One for each record of Recalls, on A4 paper.
A reminder from {Business.Name}
To · Patient number · Address · Town · Postcode
Dear {Patient.First Name},
This is a friendly reminder that you are due to come back and see us for your {Service.Name}, around {Due Date}. It takes about {Service.Minutes} minutes.
To book a time that suits you, ring us on {Business.Phone}, write to {Business.E-mail}, or book online at {Business.Website}.
If you have already booked, or have had this done elsewhere, please ignore this letter, or tell us and we will update our records. If you would rather not have reminders from us, just let us know.
With best wishes,
The team at {Business.Name}
Insurer statement
Sent to each insurer: every claim made to it, what it has paid and the balance owing. The Self-pay record prints an empty one, which can be left out.
One for each record of Insurers, on A4 paper.
Statement of claims
Insurer · Terms (days) · Address · Claims e-mail · Town · Postcode
A line for each record of Invoices: Invoice, Date, Patient, Policy, Service, Due, Claimed, Paid, Owing
Claimed · Paid · Balance owing
Please pay the balance owing to {Business.Bank Details}, quoting each invoice number, and send the remittance list to {Business.E-mail}. If you have paid since this statement was made, thank you.
Back to the overview of Cedar Lane Clinic