Marigold Hair and Beauty

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 next 7 days

Every booked appointment from today to a week ahead, by time, with the client's phone number. Use it to send reminders and plan the week.

From Bookings, Clients, Staff. Shows When, Stylist, Client, Phone, Minutes, Deposit, Booked Via.

Calculated: When is Left([Start], 10) & " " & Mid([Start], 12, 5); Stylist is [Staff].[First Name] & " " & [Staff].[Last Name]; Client is [Clients].[First Name] & " " & [Clients].[Last Name].

Only where Days Away Between 0 And 7 and Status = Booked.

Takings by stylist last month

What each member of the team took in services last calendar month, and how many services they did.

From Bookings, Staff, Booking Services. Shows Stylist, Role, Services Done, Takings, with totals.

Calculated: Stylist is [Staff].[First Name] & " " & [Staff].[Last Name].

Only where Status = Completed and Months Ago = 1.

Service commission last month

Each service done last month by someone on commission, with the price and the commission it earns. The commission statement prints it.

From Booking Services, Bookings, Staff, Clients, Services. Shows Stylist, Day, Client, Service, Price, Rate, Commission.

Calculated: Stylist is [Staff].[First Name] & " " & [Staff].[Last Name]; Day is Format([Start], "yyyy-mm-dd"); Client is [Clients].[First Name] & " " & [Clients].[Last Name]; Commission is Round([Price] * [Service Commission], 2).

Only where Service Commission > 0 and Status = Completed and Months Ago = 1.

Retail commission last month

Retail each person sold last month and the commission due on it.

From Product Sales, Staff, Products. Shows Stylist, Items Sold, Retail Sales, Rate, Commission, with totals.

Calculated: Stylist is [Staff].[First Name] & " " & [Staff].[Last Name]; Retail Sales is Round([Quantity] * [Unit Price], 2); Commission is Round([Quantity] * [Unit Price] * [Retail Commission], 2).

Only where Retail Commission > 0 and Months Ago = 1.

Service takings by month

Service takings for each month, completed appointments only. Shows the busy and quiet months.

From Bookings, Booking Services. Shows Month, Services Done, Takings, with totals.

Calculated: Month is Format([Start], "yyyy-mm").

Only where Status = Completed.

Retail sales by month

Retail sold each month and the profit on it after cost.

From Product Sales, Products. Shows Month, Items Sold, Sales, Profit, with totals.

Calculated: Month is Format([Date], "yyyy-mm"); Sales is Round([Quantity] * [Unit Price], 2); Profit is Round([Quantity] * ([Unit Price] - [Cost Price]), 2).

Clients to win back

Clients who agreed to hear from the salon, last seen between three months and a year ago, with nothing booked. The ones to send an offer to.

From Clients, Bookings. Shows Client, Phone, E-mail, Last Visit, Visits, Days Since, with totals.

Calculated: Client is [Clients].[First Name] & " " & [Clients].[Last Name]; Last Visit is Format([Start], "yyyy-mm-dd"); Days Since is DateDiff("d", [Start], Date()).

Only where Days Since Between 91 And 365 and Status In ("Completed", "Booked") and Marketing Consent = Yes.

Most booked services

Which services were done most over the last year, and what each took.

From Booking Services, Services, Bookings. Shows Service, Category, Times Done, Takings, with totals.

Only where Status = Completed and Days Ago Between 0 And 365.

Products to order

Retail and back-bar products at or below their reorder level, by supplier, with what the order will cost.

From Products, Suppliers. Shows Supplier, Product, Kind, Size, In Stock, Reorder Level, Reorder Quantity, Order Cost.

Calculated: Order Cost is Round([Reorder Quantity] * [Cost Price], 2).

Only where Above Reorder Level <= 0 and Active = Yes.

No-shows by client

Clients who have missed an appointment, with their late cancellations and the deposits kept. Use it before taking another booking without a deposit.

From Bookings, Clients. Shows Client, Phone, No-shows, Late Cancellations, Deposits Kept, with totals.

Calculated: Client is [Clients].[First Name] & " " & [Clients].[Last Name]; No-shows is IIf([Status] = "No-show", 1, 0); Late Cancellations is IIf([Status] = "Cancelled" And DateDiff("d", [Cancelled On], [Start]) < 2, 1, 0); Deposits Kept is IIf([Deposit Refunded], 0, Nz([Deposit])).

Only where No-shows >= 1 and Status In ("No-show", "Cancelled").

Patch tests due

Colour and lash appointments in the next two weeks where the client has had no patch test in the six months before. Call them in for one at least two days ahead.

From Bookings, Clients, Booking Services, Services. Shows When, Client, Phone, Last Patch Test, Service, with totals.

Calculated: When is Left([Start], 10) & " " & Mid([Start], 12, 5); Client is [Clients].[First Name] & " " & [Clients].[Last Name].

Only where Patch Test = Yes and Status = Booked and Days Away Between 0 And 14 and Days Since Test > 180.

Vouchers outstanding

Gift vouchers sold and not yet used, soonest to expire first. What the salon still owes in services.

From Gift Vouchers. Shows Code, Sold On, Bought By, For, Value, Expires, Days Left.

Calculated: Days Left is DateDiff("d", Date(), [Expires]).

Only where Days Left >= 0 and Redeemed On Is Null.

New clients by source

New clients over the last year by how they heard of the salon.

From Clients. Shows Heard Of Us, New Clients, with totals.

Only where Days Ago Between 0 And 365.

Retail best sellers

Retail products over the last year, by the profit they made.

From Product Sales, Products. Shows Product, Category, Sold, Sales, Profit, with totals.

Calculated: Sales is Round([Quantity] * [Unit Price], 2); Profit is Round([Quantity] * ([Unit Price] - [Cost Price]), 2).

Only where Days Ago Between 0 And 365.

Service menu

The services on offer, by category, with the price at each level.

From Services. Shows Category, Service, Minutes, Junior Price, Stylist Price, Senior Price.

Only where Active = Yes.

VAT by month

For each month, how many receipts were given, the takings, and the net and VAT within them: the figures for the VAT return, three months at a time.

From Receipts. Shows Month, Receipts, Takings, Net, VAT, with totals.

Calculated: Month is Format([Taken], "yyyy-mm").

Receipts last 7 days

Every receipt of the last week, with the client, the stylist, the total and what was paid at the desk and how. Use it to find a receipt to print again.

From Receipts, Bookings, Clients, Staff. Shows Number, Taken At, Client, Stylist, Total, Paid Today, Paid By.

Calculated: Taken At is Left([Taken], 10) & " " & Mid([Taken], 12, 5); Client is [Clients].[First Name] & " " & [Clients].[Last Name]; Stylist is [Staff].[First Name] & " " & [Staff].[Last Name].

Only where Days Ago Between 0 And 7.

Forms

Every table has a form of the same name: Start Here, Business, Suppliers, Staff, Services, Clients, Bookings, Booking Services, Products, Product Sales, Stock Deliveries, Gift Vouchers, Receipts, Receipt Lines, Commission Statements, Commission 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.

The column: the next 7 days by stylist

The appointment book for the next seven days, a section for each stylist, with times and phone numbers. Print it for the desk.

From the query Bookings next 7 days, a group for each Stylist: When, Client, Phone, Minutes, Deposit, with the total of Minutes.

Commission statement: last month

Last month's services for each person on commission, with a total of takings and commission each. For the owner and for payroll.

From the query Service commission last month, a group for each Stylist: Day, Client, Service, Price, Commission, with the total of Price, total of Commission.

Retail commission: last month

Retail commission due for last month, one line a person.

From the query Retail commission last month: Stylist, Items Sold, Retail Sales, Rate, Commission, with the total of Retail Sales, total of Commission.

Takings by month

Service takings month by month with a grand total.

From the query Service takings by month: Month, Services Done, Takings, with the total of Services Done, total of Takings.

Products to order

The order list for the week, a section for each supplier, with the cost of each line.

From the query Products to order, a group for each Supplier: Product, Kind, Size, In Stock, Reorder Level, Reorder Quantity, Order Cost, with the total of Order Cost.

Clients to win back

A call or mailing list of lapsed clients who agreed to marketing.

From the query Clients to win back: Client, Phone, E-mail, Last Visit, Visits, Days Since, with the count of Client.

Price list

The service menu, printed by category, with the junior, stylist and senior prices.

From the query Service menu, a group for each Category: Service, Minutes, Junior Price, Stylist Price, Senior Price.

VAT on takings by month

Takings, net and VAT month by month, with the totals, for the bookkeeper.

From the query VAT by month: Month, Receipts, Takings, Net, VAT, with the total of Takings, total of Net, total of VAT.

Receipts: the last 7 days

The last seven days of receipts, with the total taken and the total paid at the desk, to check against the card machine and the cash.

From the query Receipts last 7 days: Number, Taken At, Client, Stylist, Total, Paid Today, Paid By, with the total of Total, total of Paid Today.

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.