Data Files samples / Florist
Hazel Row Flowers
Stems and their waste, bouquets and recipes, orders and deliveries, weddings and the till for a florist.
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.
- Deliveries today and tomorrow
Every delivery for today and tomorrow, by area and then by time, with the address and the recipient's phone: the driver's list and the workroom's.
From Orders, Delivery Areas. Shows Area, Due Date, Slot, Order, Kind, Recipient, Address, Postcode, Recipient Phone, Status.
Only where
Days Ahead Between 0 And 1andStatus <> "Cancelled".- Stems to buy for the next 3 days
The flowers and foliage that the orders booked for the next three days need, item by item, beside what is in the workroom: buy the difference, and more for the till.
From Orders, Order Lines, Products, Recipe Lines, Items. Shows Item, Kind, Unit, Stems per Unit, In Stock, Stems Needed, with totals.
Calculated: Stems Needed is
[Order Lines].[Quantity] * [Stems].Only where
Status = BookedandDays Ahead Between 0 And 3andKind In ("Flower", "Foliage").- Waste by week
Stems thrown away and what they cost, a line for each of the last twelve weeks: week 0 is the last seven days.
From Waste. Shows Weeks Ago, From, To, Stems, Cost, with totals.
Calculated: Weeks Ago is
Round((DateDiff("d", [When], Date()) - 3) / 7, 0).Only where
Weeks Ago Between 0 And 11.- Waste by item
What has been thrown away in the last four weeks, item by item, the costliest first: the items to buy less of.
From Waste, Items. Shows Item, Kind, Stems, Cost, with totals.
Only where
Days Ago Between 0 And 27.- Weddings in the next 60 days
Booked weddings in the next sixty days whose balance has not been paid, with the balance and how many days until it is due (below nought when it is late).
From Weddings, Customers. Shows Wedding Date, Days to Go, Couple, Phone, Ceremony Venue, Total, Deposit, Balance, Balance Due On, Days Until Due.
Calculated: Days to Go is
DateDiff("d", Date(), [Wedding Date]); Balance isRound([Total] - Nz([Deposit]), 2); Days Until Due isDateDiff("d", Date(), [Balance Due On]).Only where
Days to Go Between 0 And 60andStatus = BookedandBalance Paid On Is Null.- Margin per product
Each product on sale with its price, what its stems cost at the last market price, and the margin left after VAT and stems, the best first.
From Recipe Lines, Products, Items. Shows Kind, Product, Price, Cost of Stems, Margin, with totals.
Calculated: Cost of Stems is
Round([Stems] * [Cost per Stem], 2); Margin isIIf([Line] = 1, Round([Price] / (1 + [Tax Rate]), 2), 0) - Round([Stems] * [Cost per Stem], 2).Only where
On Sale = Yes.- Takings by occasion
Orders paid in the last twelve months by occasion, with what they brought in.
From Orders. Shows Occasion, Orders, Takings, VAT, with totals.
Only where
Status <> "Cancelled"andDays Ago Between 0 And 365.- Till takings by day
Receipts, takings and VAT at the till for each of the last fourteen days.
From Till Sales. Shows Day, Receipts, Takings, VAT, with totals.
Calculated: Day is
Format([Sold At], "yyyy-mm-dd").Only where
Days Ago Between 0 And 13.- Orders by month
Orders by the month they were delivered, with their takings and the delivery charges.
From Orders. Shows Month, Orders, Takings, Delivery Charges, with totals.
Calculated: Month is
Format([Due Date], "yyyy-mm").Only where
Status <> "Cancelled".- Deliveries by area
Deliveries in the last ninety days by area, with the charges collected: whether each area's charge is right.
From Orders, Delivery Areas. Shows Area, Charge, Deliveries, Charges, with totals.
Only where
Status = DeliveredandDays Ago Between 0 And 90.- Market spend by month
What was spent with each supplier each month.
From Purchases, Suppliers. Shows Month, Supplier, Purchases, Spent, with totals.
Calculated: Month is
Format([Bought On], "yyyy-mm").- Customers who order most
Customers who have spent 150 or more on orders, the biggest first.
From Customers, Orders. Shows Customer, Phone, Kind, Orders, Spent, with totals.
Calculated: Customer is
IIf(Nz([Company], "") = "", [Customers].[First Name] & " " & [Customers].[Last Name], [Company]).Only where
Total >= 150andStatus <> "Cancelled".
Forms
Every table has a form of the same name: Start Here, Business, Staff, Suppliers, Items, Products, Recipe Lines, Purchases, Purchase Lines, Waste, Delivery Areas, Delivery Runs, Customers, Orders, Order Lines, Till Sales, Till Lines, Weddings, Wedding 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.
- Delivery sheet
Today's and tomorrow's deliveries on a page for each area, for the driver to take.
From the query Deliveries today and tomorrow, a group for each Area: Due Date, Slot, Order, Recipient, Address, Postcode, Recipient Phone, Status, with the count of Order.
- Market list
What the booked orders need from the market for the next three days, beside what is in stock.
From the query Stems to buy for the next 3 days, a group for each Kind: Item, Unit, Stems per Unit, In Stock, Stems Needed, with the total of Stems Needed.
- Weekly waste
Stems and money thrown away each week for twelve weeks, with the totals.
From the query Waste by week: Weeks Ago, From, To, Stems, Cost, with the total of Stems, total of Cost.
- Wedding balances
Balances due on the weddings of the next sixty days, and the total to come.
From the query Weddings in the next 60 days: Wedding Date, Couple, Ceremony Venue, Total, Deposit, Balance, Balance Due On, with the total of Balance.
- Product margins
The price list with the cost of stems and the margin of each product, by kind.
From the query Margin per product, a group for each Kind: Product, Price, Cost of Stems, Margin.
- Occasion takings
A year's order takings by occasion.
From the query Takings by occasion: Occasion, Orders, Takings, VAT, with the total of Orders, total of Takings.
- Till takings
The till for the last fourteen days, with the totals for cashing up and the VAT return.
From the query Till takings by day: Day, Receipts, Takings, VAT, with the total of Receipts, total of Takings, total of VAT.
- Monthly orders
Orders and their takings, month by month.
From the query Orders by month: Month, Orders, Takings, Delivery Charges, with the total of Orders, total of Takings.
- 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.
Order confirmation
The letter or e-mail sent when an order is taken: what will be made, for whom, where and when, the card's words, what was paid, and how to change or cancel.
One for each record of Orders, on A4 paper.
To · Order · Address · Taken · Town · Postcode
Your order {Number}
Dear {Customer.First Name},
Thank you for your order. This is what we have written down. If anything is not right, call us on {Business.Phone} and we will put it right.
A line for each record of Order Lines: Flowers, Quantity, Price, Amount
Flowers · Delivery · Total · VAT included
Delivery or collection · On · For · Time · Address · Town · Postcode · What we will make
The card will read: {Card Message} {Card From}
Paid on {Paid On}: {Payment}.
Flowers are made fresh on the day from what is best at the market, so a stem may differ a little from the picture; the colours and the size will not. If you need to change or cancel the order, tell us by 5pm the day before and we refund you in full. Once the flowers are made we can change the delivery but cannot refund them.
With best wishes,
{Taken By}, {Business.Name}
{Business.Document Footer}
Gift card message
The small card that goes with the flowers: the message and who it is from, nothing else.
One for each record of Orders, on A5 paper.
{Card Message}
{Card From}
Delivery note
Goes with the driver: the recipient, the address, the time, whether a card is enclosed and what is being delivered, with no prices, and a line for whoever takes them in to sign.
One for each record of Orders, on A5 paper.
Delivery note {Number}
For · Delivery date · Address · Time · Town · Area · Postcode · Driver · Phone · Card enclosed
A line for each record of Order Lines: Flowers, Quantity
Items
Someone has sent you flowers from {Business.Name}. To keep them fresh, cut the stems at an angle, put them in clean water and keep them out of direct sun. Change the water every two days.
Signed: Received by, Time
Wedding quote
Sent after the consultation: the couple, the date and venues, each piece with its price, the total with its VAT, and the deposit to book.
One for each record of Weddings, on A4 paper.
Wedding flowers: quote {Quote No}
For · Quote · Address · Date · Postcode · Prices hold until · The couple · Wedding date · Ceremony · Reception · Colours
A line for each record of Wedding Lines: Description, Quantity, Each, Amount, VAT within
Total for the flowers · VAT included · Deposit to book (a quarter)
To book, pay the deposit above: we then hold the date for you and send an invoice. The balance is due four weeks before the wedding. Flowers are seasonal: if a flower is not at its best that week we use the closest we can find, and we tell you first.
The price includes making everything, delivery on the morning of the wedding, and setting up at the ceremony and the reception. Vases and stands we bring are collected the next working day.
{Business.Document Footer}
Wedding invoice
Sent when a wedding is booked: the same lines, the deposit paid and the balance due four weeks before the day. A wedding not yet booked prints with no invoice number.
One for each record of Weddings, on A4 paper.
Invoice {Invoice No}
Invoice to · Invoice · Address · Date · Town · Your quote · Postcode · Wedding date · The couple · Balance due by · Deposit paid on · Balance paid on
A line for each record of Wedding Lines: Description, Quantity, Each, Amount, VAT within
Total for the flowers · VAT included · Deposit paid · Balance due
Please pay the balance by {Balance Due On} to {Business.Bank Details}, quoting {Invoice No}. If you have paid it since this invoice was sent, thank you.
{Business.Payment Terms}
Till receipt
The receipt from the till, on an 80 mm roll: what was bought, the total and the VAT within it.
One for each record of Till Sales, on a till roll 80 mm wide paper.
Receipt {Number}
Date · Served by · Paid by
A line for each record of Till Lines: Item, Qty, Amount
Total · VAT at 20% included
{Business.Document Footer}
Back to the overview of Hazel Row Flowers