Data Files samples / Gym and fitness studio
Tannery Yard Fitness
Members on monthly plans, direct debits, classes and bookings, personal training packs and visits for an independent gym.
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.
- Classes in the next 7 days
Classes on the timetable for the next week that still have places, with how many are booked and free. For the desk, the website and the weekly e-mail.
From Class Sessions, Classes, Staff. Shows Starts, Day, Time, Class, Instructor, Studio, Capacity, Booked, Free Places.
Calculated: Day is
Format([Starts], "yyyy-mm-dd"); Time isMid([Starts], 12, 5); Instructor is[First Name] & " " & [Last Name]; Free Places is[Class Sessions].[Capacity] - [Places Booked].Only where
Free Places > 0andDays Ahead Between 0 And 7andStatus = Scheduled.- Class register
Everyone booked into each class in the next 7 days, with their membership number and phone, for the instructor's register.
From Bookings, Class Sessions, Classes, Members. Shows Class, Member, Member No, Phone, Booked At.
Calculated: Class is
[Classes].[Name] & ", " & Format([Starts], "yyyy-mm-dd") & " " & Mid([Starts], 12, 5); Member is[First Name] & " " & [Last Name].Only where
Status = BookedandDays Ahead Between 0 And 7.- Payments to chase
Invoices whose direct debit failed, either waiting for the second try or failed twice, with the member's phone and e-mail and how long ago the payment was due.
From Invoices, Members. Shows Invoice, Member, Phone, E-mail, Due Date, Total, Status, Days Overdue.
Calculated: Member is
[First Name] & " " & [Last Name]; Days Overdue isDateDiff("d", [Due Date], Date()).Only where
Status In ("Retry due", "Failed").- Not seen for 30 days
Active members whose last visit was 30 days ago or more, the longest gone first. These are the members most likely to cancel; a call or a free session often brings them back.
From Members, Plans, Visits. Shows Member, Member No, Plan, Phone, E-mail, Last Visit, Days Since, with totals.
Calculated: Member is
[First Name] & " " & [Last Name]; Days Since isDateDiff("d", [Visited At], Date()).Only where
Days Since >= 30andStatus = Active.- Attendance by class
For each class over the last year: places booked, how many came, no-shows and late cancellations. Shows which classes to add to and which to change.
From Bookings, Class Sessions, Classes. Shows Class, Booked, Came, No-shows, Cancelled, with totals.
Calculated: Came is
IIf([Bookings].[Status] = "Attended", 1, 0); No-shows isIIf([Bookings].[Status] = "No-show", 1, 0); Cancelled isIIf([Bookings].[Status] = "Cancelled", 1, 0).Only where
Status = HeldandDays Ago Between 0 And 365.- Instructor pay
The classes each instructor taught each month and what they are owed for them. Cancelled classes are not paid.
From Class Sessions, Staff. Shows Month, Instructor, Classes, Pay, with totals.
Calculated: Month is
Format([Starts], "yyyy-mm"); Instructor is[First Name] & " " & [Last Name].Only where
Status = Held.- Trainer pay
The personal training sessions each trainer gave each month, missed ones included, and what they are owed.
From PT Sessions, Staff. Shows Month, Trainer, Sessions, Pay, with totals.
Calculated: Month is
Format([Session At], "yyyy-mm"); Trainer is[First Name] & " " & [Last Name].Only where
Status In ("Done", "No-show").- Plan mix
How many members are on each plan now and what they bring in each month, frozen members at the freeze fee.
From Members, Plans. Shows Plan, Members, Fee, Monthly Income, with totals.
Calculated: Monthly Income is
IIf([Status] = "Frozen", [Freeze Fee], [Members].[Monthly Fee]).Only where
Status In ("Active", "Frozen").- Income by month
The fees taken each month, before VAT, the VAT, and in all, by the day they were paid.
From Invoices. Shows Month, Invoices, Before VAT, VAT, Taken, with totals.
Calculated: Month is
Format([Paid On], "yyyy-mm").Only where
Status = Paid.- Income by kind
What the last year's paid invoices brought in from monthly fees, joining fees and freeze fees.
From Invoice Lines, Invoices. Shows Kind, Lines, Taken, with totals.
Calculated: Taken is
Round([Quantity] * [Unit Price], 2).Only where
Status = PaidandDays Ago Between 0 And 365.- PT packs sold
Personal training packs sold each month, the sessions in them and the takings.
From PT Packs. Shows Month, Packs, Sessions, Takings, with totals.
Calculated: Month is
Format([Bought On], "yyyy-mm").- PT packs running low
Open personal training packs with three sessions or fewer left, soonest to expire first. The trainer offers the member a new pack.
From PT Packs, Members, Staff. Shows Pack, Member, Phone, Trainer, Sessions, Used, Left, Expires.
Calculated: Member is
[Members].[First Name] & " " & [Members].[Last Name]; Trainer is[Staff].[First Name] & " " & [Staff].[Last Name]; Left is[Sessions] - [Sessions Used].Only where
Left Between 1 And 3andStatus = Open.- Freezes now and coming
Freezes running now or booked to start, with who, why and to when.
From Freezes, Members. Shows Starts, Ends, Member, Member No, Reason, Freeze Fee.
Calculated: Member is
[First Name] & " " & [Last Name].Only where
Days Left >= 0.- Members in notice
Members who have given notice and are working it out, with the day their membership ends. A last chance to ask why and keep them.
From Members, Plans. Shows Ends, Member, Member No, Plan, Joined, Cancel Requested, Days Left, Phone.
Calculated: Member is
[First Name] & " " & [Last Name]; Days Left isDateDiff("d", Date(), [Ends]).Only where
Days Left >= 0andCancel Requested Is Not Null.- New members by month
How many joined each month on each plan.
From Members, Plans. Shows Month, Plan, Joined, with totals.
Calculated: Month is
Format([Joined], "yyyy-mm").Only where
Days Ago Between 0 And 400.
Forms
Every table has a form of the same name: Start Here, Business, Plans, Staff, Members, Freezes, Invoices, Invoice Lines, Payments, Classes, Class Sessions, Bookings, Visits, PT Packs, PT Sessions. 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.
- Classes in the next 7 days with places free
The next week's classes with free places, day by day: the list for the desk and the noticeboard.
From the query Classes in the next 7 days, a group for each Day: Time, Class, Instructor, Studio, Capacity, Booked, Free Places, with the total of Free Places.
- Class registers for the next 7 days
A register for each class in the next 7 days with who is booked, to tick off at the door.
From the query Class register, a group for each Class: Member, Member No, Phone, with the count of Member.
- Payments to chase
Failed payments with the member's phone and how overdue each is, and the total, for the manager to work through.
From the query Payments to chase: Invoice, Member, Phone, Status, Due Date, Days Overdue, Total, with the total of Total.
- Members not seen for 30 days
Members not seen for 30 days or more, with their phone, for calling.
From the query Not seen for 30 days: Member, Member No, Plan, Phone, Last Visit, Days Since, with the count of Member.
- Instructor pay by month
Each month's classes and pay for each instructor, for paying them.
From the query Instructor pay, a group for each Month: Instructor, Classes, Pay, with the total of Classes, total of Pay.
- Trainer pay by month
Each month's personal training sessions and pay for each trainer.
From the query Trainer pay, a group for each Month: Trainer, Sessions, Pay, with the total of Sessions, total of Pay.
- Members and monthly income by plan
Members and monthly income by plan, with the totals.
From the query Plan mix: Plan, Members, Fee, Monthly Income, with the total of Members, total of Monthly Income.
- Monthly income
Fees taken each month, before VAT, VAT and in all, with the totals for the accountant.
From the query Income by month: Month, Invoices, Before VAT, VAT, Taken, with the total of Before VAT, total of VAT, total of Taken.
- Class attendance over the last year
Bookings, attendance, no-shows and late cancellations for each class over the last year.
From the query Attendance by class: Class, Booked, Came, No-shows, Cancelled, with the total of Booked, total of Came.
- Personal training packs running low
Packs with three sessions or fewer left, a page for each trainer.
From the query PT packs running low, a group for each Trainer: Pack, Member, Phone, Sessions, Left, Expires, with the total of Left.
- 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.
Membership agreement
The agreement a new member signs: their plan, fee, payment day and minimum term, then numbered clauses on fees, freezing, cancelling with notice, health and the gym's rules, and two signatures. Written for this sample; have it checked for your own country before you use it.
One for each record of Members, on A4 paper.
Membership agreement
Membership no · Start date · First Name · Last Name · Date of Birth · Phone · Address · Postcode · Plan · Monthly Fee · Joining fee · Payment day each month · Minimum term ends · Health questions done · Emergency Contact · Emergency Phone
1. The parties. This agreement is between {Business.Name} of {Business.Address}, {Business.City} {Business.Postcode} ("the gym") and {First Name} {Last Name} of {Address}, {City} {Postcode} ("the member"). It starts on {Joined}.
2. The plan. The member joins on the {Plan.Name} plan: {Plan.Description}. The member may use the gym {Plan.Access Hours}. A family plan covers the people named on it at one address, and the member who signs pays the fees for all of them.
3. Fees. The joining fee of {Plan.Joining Fee} and the first month's fee are paid on joining. After that the monthly fee of {Monthly Fee} is collected by direct debit on day {Payment Day} of each month. Prices include VAT. If a payment fails, the gym tries again five days later; if that fails too, the member's card stops opening the door until the amount is paid at the desk. The gym gives at least a month's notice in writing of any change to the fee.
4. Minimum term. The plan has a minimum term of {Plan.Minimum Months} months, ending on {Minimum Term Ends}, and the monthly fee is paid for the whole of it. The gym will let a member leave early for a serious illness or injury, or a move of more than 20 miles away, if they show evidence of it.
5. Freezing. The member may freeze the membership for one to three months in any twelve, from a payment day, by asking at the desk at least ten days before it. While it is frozen the member pays {Plan.Freeze Fee} a month instead of the monthly fee, and cannot use the gym or book classes.
6. Cancelling. The member may cancel at any time, in person at the desk or in writing, giving {Plan.Notice Months} month's notice. The membership then ends the day before the first payment day that is at least that far ahead, or at the end of the minimum term if that is later; fees up to that day are still due. The gym may end a membership at once if the member seriously or repeatedly breaks the rules below.
7. Health. The member confirms that their answers to the health questions are true, that they will tell staff of any change, and that they will stop and ask for help if they feel unwell while exercising. Anyone with a health condition should ask their doctor before starting a new kind of exercise.
8. The rules. Show your card at the door and do not let anyone in on it. Wipe equipment down after use and put weights back. Wear clean indoor shoes on the gym floor. Classes are booked up to seven days ahead; a place not cancelled at least two hours before counts as a no-show, and three no-shows in a month stop booking for a fortnight. Children aged 14 to 17 on a family plan use the gym floor only with an adult on the same plan. Lockers are emptied each night.
9. Personal details. The gym keeps the member's details to run the membership, collect the fees and contact them about it. It passes them to no one but its bank, for the direct debit. The member may ask to see what is kept about them.
10. Belongings and injury. The gym is not responsible for belongings left in the building unless it has been careless with them. Nothing here limits the gym's responsibility for injury caused by its own negligence.
Signed: Member, For the gym
{Business.Name}, {Business.Address}, {Business.City} {Business.Postcode}. VAT number {Business.Tax Number}.
Membership card
A small card with the member's name, membership number, plan and the times they may come in, printed when they join.
One for each record of Members, on a till roll 58 mm wide paper.
{First Name} {Last Name}
Member no · Plan · Member since · Access
Show this card at the door. It is for the named member only. A lost card is replaced at the desk.
Welcome letter
Handed over or posted when someone joins: their membership number, fee and payment day, minimum term, how to book an induction and classes, and personal training.
One for each record of Members, on A4 paper.
First Name · Membership no · Last Name · Joined on · Address · Plan · Town · Postcode
Welcome to {Business.Name}
Dear {First Name},
Thank you for joining us, and welcome. Your membership number is {Number}, and it is on your card; show the card at the door each time you come in. On the {Plan.Name} plan you can use the gym {Plan.Access Hours}.
Your monthly fee of {Monthly Fee} will be collected by direct debit on day {Payment Day} of each month, and an invoice comes by e-mail ten days before. Your minimum term ends on {Minimum Term Ends}; after that you can cancel with a month's notice, or freeze for up to three months if you are injured or away.
Your first visit should be a gym induction with one of our instructors, which is free and takes about forty minutes: ask at the desk or call {Business.Phone} to book one. Classes are on the timetable by the door and can be booked up to seven days ahead; please cancel at least two hours before if you cannot come, so someone else can have the place.
If you would like a programme of your own, our personal trainers sell sessions in packs of five, ten or twenty, and your first consultation with one is free.
We are glad to have you. If anything is not right, tell any of us, or write to {Business.E-mail}.
With best wishes,
Signed: Gym manager
{Business.Document Footer}
Monthly invoice
Each month's invoice, sent ten days before the payment day: the period it covers, each line with the VAT in its price, and the total collected. The first one has the joining fee on it. A failed payment shows Retry due or Failed.
One for each record of Invoices, on A4 paper.
Invoice {Number}
Member · Invoice no · Address · Invoice date · Town · Collected on · Postcode · Member no · Period from · Period to · Status · Paid on
A line for each record of Invoice Lines: Description, Quantity, Unit Price, Amount, VAT included
Price before VAT · VAT at 20% · Total
{Business.Payment Terms}
Prices include VAT. VAT number {Business.Tax Number}. {Business.Document Footer}
PT pack receipt
The till receipt for a pack of personal training: the trainer, the sessions in it, the use-by date, the price per session and the VAT within the price.
One for each record of PT Packs, on a till roll 80 mm wide paper.
Receipt {Number}
Date · Member · Member no · Trainer · Personal training sessions · Use by · Paid by
Price per session · VAT included · Total paid
Book your sessions with {Trainer} at the desk or on {Business.Phone}. A session cancelled with less than 24 hours' notice counts as used. Sessions not used by the use-by date end then and are not refunded.
Prices include VAT. VAT number {Business.Tax Number}. Thank you.
Back to the overview of Tannery Yard Fitness