Data Files samples / Garage and MOT centre
Quarrybank Motors
Customers, vehicles, bookings, job cards, MOT tests, services, reminders, parts and invoices for an independent garage.
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.
- Bookings today and tomorrow
The diary for today and tomorrow, in time order, with the registration, the customer's number, the work and whether they need the courtesy car. Cancelled bookings are left out. Open it first thing.
From Bookings, Vehicles, Customers. Shows Date, Time, Registration, Vehicle, Customer, Phone, Work, Courtesy Car, Status.
Calculated: Vehicle is
[Make] & " " & [Model]; Customer is[Customers].[First Name] & " " & [Customers].[Last Name].Only where
Days Ahead Between 0 And 1andStatus <> "Cancelled".- Bookings next 7 days
How many vehicles are booked in for each of the next seven days, how many are MOTs and how many need the courtesy car, to see where there is room.
From Bookings, Vehicles, Customers. Shows Date, Bookings, MOTs, Courtesy Cars, with totals.
Calculated: MOTs is
IIf(Left([Work], 3) = "MOT", 1, 0); Courtesy Cars isIIf([Courtesy Car], 1, 0).Only where
Days Ahead Between 0 And 7andStatus <> "Cancelled".- MOTs due next month
Every vehicle whose MOT runs out next month, with the customer's address and how they like reminders: the list to send reminder letters from.
From Vehicles, Customers. Shows MOT Due, Registration, Vehicle, Customer, Address, City, Postcode, Phone, Remind By.
Calculated: Vehicle is
[Make] & " " & [Model]; Customer is[Customers].[First Name] & " " & [Customers].[Last Name].Only where
Months Ahead = 1.- Services due next 30 days
Vehicles whose yearly service falls due in the next thirty days, with their last mileage.
From Vehicles, Customers. Shows Service Due, Registration, Vehicle, Mileage, Customer, Phone, Remind By, Days Left.
Calculated: Vehicle is
[Make] & " " & [Model]; Customer is[Customers].[First Name] & " " & [Customers].[Last Name]; Days Left isDateDiff("d", Date(), [Service Due]).Only where
Days Left Between 0 And 30.- Reminders to send
Reminders made ready and not yet sent, by due date, with how each customer wants them. Print the Reminder letter for the letters, then fill in Sent On.
From Reminders, Vehicles, Customers. Shows Due, Kind, Registration, Customer, Method, E-mail, Phone.
Calculated: Customer is
[Customers].[First Name] & " " & [Customers].[Last Name].Only where
Sent On Is Null.- Advisories not fixed
Advisories from MOT tests in the last year that have not been put right, by registration: worn tyres, thin brake pads, a little corrosion. Ring the customer, or bring it up at the next visit.
From MOT Items, MOT Tests, Vehicles, Customers. Shows Registration, Vehicle, Customer, Phone, Test Date, Advisory.
Calculated: Vehicle is
[Make] & " " & [Model]; Customer is[Customers].[First Name] & " " & [Customers].[Last Name].Only where
Kind = AdvisoryandFixed = NoandDays Ago Between 0 And 365.- Open job cards
Every job card not yet closed, oldest first, with the vehicle, the technician, its status and how many days it has been open.
From Job Cards, Vehicles, Customers, Staff. Shows Job Card, Opened, Registration, Vehicle, Customer, Phone, Work Requested, Technician, Status, Days Open.
Calculated: Vehicle is
[Make] & " " & [Model]; Customer is[Customers].[First Name] & " " & [Customers].[Last Name]; Technician is[Staff].[First Name] & " " & [Staff].[Last Name]; Days Open isDateDiff("d", [Opened], Date()).Only where
Closed Is Null.- Technician hours last 30 days
Hours of labour each technician has sold in the last thirty days, and what they came to without VAT.
From Job Lines, Staff. Shows Technician, Hours Sold, Labour Sold, Lines, with totals.
Calculated: Technician is
[Staff].[First Name] & " " & [Staff].[Last Name]; Labour Sold isRound([Quantity] * [Unit Price], 2).Only where
Kind = LabourandDays Ago Between 0 And 30.- Technician hours by month
Hours of labour sold by each technician in each month, for the workshop manager's month-end look.
From Job Lines, Staff. Shows Month, Technician, Hours Sold, Labour Sold, with totals.
Calculated: Month is
Format([Done On], "yyyy-mm"); Technician is[Staff].[First Name] & " " & [Staff].[Last Name]; Labour Sold isRound([Quantity] * [Unit Price], 2).Only where
Kind = LabourandDone On Is Not Null.- Parts margin
For each kind of part, what was sold without VAT, what it cost and the margin, the biggest first.
From Job Lines, Parts. Shows Category, Units, Sales, Cost of Parts, Margin, with totals.
Calculated: Sales is
Round([Quantity] * [Unit Price], 2); Cost of Parts isRound([Quantity] * [Unit Cost], 2); Margin isRound([Quantity] * ([Unit Price] - [Unit Cost]), 2).Only where
Done On Is Not Null.- Takings by month
Money received each month from invoices paid, private and trade together.
From Invoices. Shows Month, Invoices Paid, Takings, with totals.
Calculated: Month is
Format([Paid On], "yyyy-mm").Only where
Paid On Is Not Null.- VAT by month
For each month, the invoices raised, their net value, the VAT and the total: the figures for the VAT return.
From Invoices. Shows Month, Invoices, Net Sales, VAT, Invoiced, with totals.
Calculated: Month is
Format([Invoice Date], "yyyy-mm").- MOT results by month
How many MOT tests were done each month, how many passed and failed, and how many were retests.
From MOT Tests. Shows Month, Tests, Passed, Failed, Retests, with totals.
Calculated: Month is
Format([Test Date], "yyyy-mm"); Passed isIIf([Result] = "Pass", 1, 0); Failed isIIf([Result] = "Fail", 1, 0); Retests isIIf(Nz([Retest Of], 0) > 0, 1, 0).- Service history
Every service carried out, vehicle by vehicle, newest first, with the mileage, the job card and when the next is due.
From Services, Job Cards, Vehicles. Shows Registration, Vehicle, Service Date, Kind, Mileage, Job Card, Next Due, Next Due Mileage.
Calculated: Vehicle is
[Make] & " " & [Model].- Estimates awaiting answer
Estimates the customer has not yet said yes or no to, oldest first, with the phone number to follow up.
From Estimates, Vehicles, Customers. Shows Estimate, Estimate Date, Registration, Customer, Phone, Work, Total, Valid Until.
Calculated: Customer is
[Customers].[First Name] & " " & [Customers].[Last Name].Only where
Answer Is Null.- Parts to reorder
Shelf parts at or below their reorder level, by supplier, with what a full reorder would cost.
From Parts, Suppliers. Shows Supplier, Code, Part, In Stock, Reorder Level, Reorder Quantity, Cost, Order Value.
Calculated: Order Value is
Round([Reorder Quantity] * [Cost], 2).Only where
Spare <= 0andStocked = Yes.- Trade accounts to chase
Every trade invoice not yet paid, by account and due date, with the registration and how many days overdue it is (nought when it is not due yet).
From Invoices, Accounts, Job Cards, Vehicles. Shows Account, Contact, Phone, Invoice, Registration, Invoice Date, Due Date, Total, Days Overdue.
Calculated: Days Overdue is
IIf(DateDiff("d", [Due Date], Date()) > 0, DateDiff("d", [Due Date], Date()), 0).Only where
Paid On Is Null.
Forms
Every table has a form of the same name: Start Here, Business, Accounts, Customers, Staff, Suppliers, Parts, Vehicles, Bookings, Job Cards, Part Orders, Job Lines, MOT Tests, MOT Items, Services, Reminders, Estimates, Estimate Lines, Invoices. 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.
- Bookings: today and tomorrow
The diary for today and tomorrow, one section a day in time order: pin it up in the workshop and keep one at the desk.
From the query Bookings today and tomorrow, a group for each Date: Time, Registration, Vehicle, Customer, Phone, Work, Courtesy Car, Status, with the count of Registration.
- MOTs due next month
Every vehicle with its MOT due next month and the address to write to, for checking the reminder letters before they go.
From the query MOTs due next month: MOT Due, Registration, Vehicle, Customer, Address, City, Postcode, Remind By, with the count of Registration.
- Advisories to follow up
Unfixed advisories, a section for each vehicle, with the customer's number: the list for a quiet afternoon on the phone.
From the query Advisories not fixed, a group for each Registration: Test Date, Advisory, Customer, Phone, with the count of Advisory.
- Work in progress
Open job cards, a section for each status, with the technician and how long each has been open.
From the query Open job cards, a group for each Status: Job Card, Opened, Registration, Vehicle, Work Requested, Technician, Days Open, with the count of Job Card.
- Hours sold by technician, by month
Hours and labour sold by each technician, a section for each month, with the totals.
From the query Technician hours by month, a group for each Month: Technician, Hours Sold, Labour Sold, with the total of Hours Sold, total of Labour Sold.
- Takings by month
Money received each month with a grand total.
From the query Takings by month: Month, Invoices Paid, Takings, with the total of Invoices Paid, total of Takings.
- VAT by month, from the invoices
Net sales, VAT and invoiced totals for each month, with the grand totals, for the VAT return or the accountant.
From the query VAT by month: Month, Invoices, Net Sales, VAT, Invoiced, with the total of Net Sales, total of VAT, total of Invoiced.
- Parts to order
The parts to reorder, a page for each supplier, with the value of each order.
From the query Parts to reorder, a group for each Supplier: Code, Part, In Stock, Reorder Level, Reorder Quantity, Cost, Order Value, with the total of Order Value.
- Trade invoices due
Unpaid trade invoices, a section for each account, with days overdue and the total owed.
From the query Trade accounts to chase, a group for each Account: Invoice, Registration, Invoice Date, Due Date, Days Overdue, Total, with the total of Total.
- 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.
Estimate
The written estimate for a repair: the vehicle, the work, the labour and parts with VAT, the total and how long it holds. One for each record in Estimates.
One for each record of Estimates, on A4 paper.
Estimate {Number}
Estimate · Date · Valid until · For · Address · Postcode · Registration · Make · Model · Mileage
Work to be done
A line for each record of Estimate Lines: Item, Description, Qty or hours, Price, Net, VAT rate, VAT
Net · VAT · Estimated total
This estimate is our price for the work above, and holds until {Valid Until}. If we find anything else once the work starts, we will ring you before we do it: nothing more is done without your say-so.
To book the work in, call us on {Business.Phone} quoting {Number}.
{Business.Document Footer}
Job card
Printed when a vehicle arrives and kept with its keys: the vehicle, mileage and work asked for, then the work done and parts fitted with who did them, and two lines to sign. Prices are left off, as the workshop does not need them. One for each record in Job Cards; print the one on screen.
One for each record of Job Cards, on A4 paper.
Job card {Number}
Job card · Opened · Technician · Registration · Make · Model · Fuel · Year · Mileage in · Customer · Phone · Tyres
Work requested · Notes
A line for each record of Job Lines: Kind, Work done or part fitted, Part, Qty or hours, By, Date
Before work starts: check the mileage, note any damage and warning lights, and fit seat and floor covers. Any work beyond what is asked for above is authorised by the customer first, and the call is noted here.
Signed: Technician, Road test and quality check
Invoice
The VAT invoice for a job card: the labour, parts and MOT fee with the VAT on each line, the net, VAT and total, and how to pay. One for each record in Invoices.
One for each record of Invoices, on A4 paper.
VAT invoice {Number}
Invoice · Date and tax point · Due · Job card · Customer · Trade account · Address · Postcode · Registration · Make · Model · Mileage
A line for each record of Job Lines: Item, Description, Qty or hours, Price, Net, VAT rate, VAT
Net · VAT · Total
The MOT test fee carries no VAT. {Business.Payment Terms} By bank transfer to {Business.Bank Details}, quoting {Number}.
{Business.Document Footer}
Reminder letter
The letter sent four weeks before an MOT or service is due: short and friendly, with the registration, the due date and how to book. One for each record in Reminders; print those in the query Reminders to send. Have the wording checked for your own country.
One for each record of Reminders, on A4 paper.
To · Address · Town · Postcode
{Vehicle}: {Kind} due on {Due}
Dear {Vehicle.Customer.First Name} {Vehicle.Customer.Last Name},
Our records show that your {Vehicle.Make} {Vehicle.Model}, registration {Vehicle}, is due its {Kind} on {Due}. We are writing in good time so that you can book a day that suits you.
An MOT can be done up to a month before the due date without losing any time on the certificate, and if your service is due too we can do both on the same visit. A courtesy car can be booked if you need one, and we can collect the car from a local address.
To book, ring us on {Business.Phone}, write to {Business.E-mail}, or call in. If the car has been sold, or the work done elsewhere, just let us know and we will update our records.
Yours sincerely,
Signed: Service desk
{Business.Document Footer}
Trade statement
Sent to each trade account at the month end: every invoice raised to the account with the day it was paid, what is still owing and how much is overdue. One for each record in Accounts.
One for each record of Accounts, on A4 paper.
Statement of account
Account · For the attention of · Address · City · Postcode · Days to pay · Credit limit
A line for each record of Invoices: Invoice, Registration, Date, Due, Amount, Paid on, Owing, Overdue
Of which overdue · Balance owing
Invoices are due {Payment Days} days from their date. Please pay by bank transfer to {Business.Bank Details}, quoting the invoice numbers. If you have paid in the last few days, thank you, and please ignore what is shown as owing.
{Business.Document Footer}
Back to the overview of Quarrybank Motors