Data Files samples / Cafe or restaurant
Little Fig Cafe
Menu, recipes and food costs, till orders, rota, deliveries, waste and the nightly cash-up for a small cafe.
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.
- Recipe card
Every line of every recipe with what it costs, for checking a recipe or showing a new cook how a dish is made up.
From Recipe Lines, Menu Items, Ingredients. Shows Menu Item, Line, Ingredient, Quantity, Unit, Cost per Unit, Line Cost.
Calculated: Line Cost is
Round([Quantity] * [Cost per Unit], 2).Only where
Active = Yes.- Best sellers, last 30 days
How many of each dish sold in the last 30 days and what they took, most sold first. Use it to plan the bake and the prep, and to spot dishes worth dropping.
From Order Lines, Orders, Menu Items, Menu Categories. Shows Menu Item, Category, Sold, Takings, with totals.
Calculated: Takings is
Round([Quantity] * [Unit Price], 2).Only where
Days Ago Between 0 And 30andVoided = No.- Sales by hour
Bills, takings and the average bill for each hour of the day over the last eight weeks: when the rush comes and when the cafe is quiet, for setting shift times.
From Orders. Shows Hour, Orders, Takings, Average Spend, with totals.
Calculated: Hour is
Mid([When], 12, 2).Only where
Days Ago Between 1 And 56andVoided = No.- Sales by day of the week
Bills, covers, takings and the average bill for each day of the week over the last eight weeks, for planning who works when.
From Orders. Shows Day, Orders, Covers, Takings, Average Spend, with totals.
Calculated: Day is
IIf(DateDiff("d", #2024-01-01#, [When]) Mod 7 = 0, "Monday", IIf(DateDiff("d", #2024-01-01#, [When]) Mod 7 = 1, "Tuesday", IIf(DateDiff("d", #2024-01-01#, [When]) Mod 7 = 2, "Wednesday", IIf(DateDiff("d", #2024-01-01#, [When]) Mod 7 = 3, "Thursday", IIf(DateDiff("d", #2024-01-01#, [When]) Mod 7 = 4, "Friday", IIf(DateDiff("d", #2024-01-01#, [When]) Mod 7 = 5, "Saturday", "Sunday")))))).Only where
Days Ago Between 1 And 56andVoided = No.- Average spend by service
For the last 30 days, how many bills were eaten in and how many taken away, what each brought in and the average bill for each.
From Orders. Shows Service, Orders, Covers, Takings, Average Spend, with totals.
Only where
Days Ago Between 0 And 30andVoided = No.- Takings and wages by week
Each week's takings from the cash-up sheets against the wages on the rota, what was left after wages, and the average day's wages as a share of its takings. The week starts on Monday; the newest week comes first.
From Cash Ups. Shows Week Beginning, Days Open, Orders, Covers, Takings, Wages, Left After Wages, Average Day Wage %, with totals.
Calculated: Takings is
[Card Takings] + [Cash Takings]; Left After Wages isRound([Card Takings] + [Cash Takings] - [Wage Cost], 2); Average Day Wage % isRound(100 * [Wage Cost] / ([Card Takings] + [Cash Takings]), 1).- Takings by month
Takings, the VAT within them and the takings less VAT, and wages, for each month, oldest first: to see the seasons, compare with the same month last year, and fill in the VAT return.
From Cash Ups. Shows Month, Days Open, Orders, Takings, VAT, Takings less VAT, Wages, Left After Wages, with totals.
Calculated: Month is
Format([Date], "yyyy-mm"); Takings is[Card Takings] + [Cash Takings]; Takings less VAT isRound([Card Takings] + [Cash Takings] - [VAT], 2); Left After Wages isRound([Card Takings] + [Cash Takings] - [Wage Cost], 2).- Takings by weather
The average day's takings and covers in the warmer months (May to September) by the weather noted on the cash-up sheet.
From Cash Ups. Shows Weather, Days, Average Takings, Average Covers, with totals.
Calculated: Average Takings is
[Card Takings] + [Cash Takings].Only where
Month In ("05", "06", "07", "08", "09").- Cash over or short
The days the cash counted at close did not match the till's cash reading, and by how much, with who closed up.
From Cash Ups, Staff. Shows Date, Closed By, Cash Takings, Cash Counted, Over or Short, Notes.
Calculated: Closed By is
[First Name] & " " & [Last Name]; Over or Short isRound([Cash Counted] - [Cash Takings], 2).Only where
Over or Short <> 0.- Hours and wages by staff, last 28 days
Each person's shifts, hours and wages over the last four weeks, with holiday paid in and sick days left out. Use it for the payroll and to keep hours fair.
From Shifts, Staff. Shows Name, Role, Hourly Rate, Shifts, Hours, Wages, with totals.
Calculated: Name is
[First Name] & " " & [Last Name]; Wages isRound([Paid Hours] * [Hourly Rate], 2).Only where
Days Ago Between 1 And 28andStatus In ("Worked", "Holiday").- Rota for the next 7 days
Who is on, when and where, today and for the next six days, earliest first.
From Shifts, Staff. Shows Day, From, To, Name, Station, Paid Hours, Status.
Calculated: Day is
Format([Start], "yyyy-mm-dd"); From isMid([Start], 12, 5); To isMid([End], 12, 5); Name is[First Name] & " " & [Last Name].Only where
Days Ahead Between 0 And 6.- Ingredients to order
Ingredients at or below their reorder level with nothing on order, by supplier, with the usual amount to order and what it will cost. Work through it each morning before phoning the orders in.
From Ingredients, Suppliers. Shows Supplier, Delivers On, Ingredient, Unit, In Stock, On Order, Reorder Level, Order, Bought As, Estimated Cost.
Calculated: Estimated Cost is
Round([Reorder Quantity] * [Cost per Unit], 2).Only where
Above Reorder Level <= 0.- Deliveries due
Orders placed with suppliers and not yet delivered, the earliest due first, with how many lines and their value.
From Purchases, Suppliers, Purchase Lines. Shows Purchase, Supplier, Ordered On, Due On, Lines, Value, with totals.
Calculated: Value is
Round([Quantity] * [Unit Cost], 2).Only where
Received On Is Null.- Bills to pay
Delivered orders whose bills are not yet paid, with the days left until each is due; below nought means it is overdue.
From Purchases, Suppliers, Purchase Lines. Shows Purchase, Supplier, Invoice Number, Received On, Days to Pay, Amount, with totals.
Calculated: Days to Pay is
[Payment Days] - DateDiff("d", [Received On], Date()); Amount isRound([Quantity] * [Unit Cost], 2).Only where
Received On Is Not NullandPaid On Is Null.- Spend by supplier by month
What was spent with each supplier each month, from the delivered orders.
From Purchases, Suppliers, Purchase Lines. Shows Month, Supplier, Deliveries, Spent, with totals.
Calculated: Month is
Format([Received On], "yyyy-mm"); Spent isRound([Quantity] * [Unit Cost], 2).Only where
Received On Is Not Null.- Waste by ingredient, last 90 days
For each ingredient thrown away in the last 90 days, how often, how much and what it cost, the dearest first. Use it to order less of what goes off.
From Waste, Ingredients. Shows Ingredient, Unit, Times, Wasted, Cost, with totals.
Calculated: Cost is
Round([Quantity] * [Cost per Unit], 2).Only where
Days Ago Between 0 And 90.- Waste in the last 30 days
Each entry on the waste sheet in the last 30 days with its cost and who recorded it.
From Waste, Ingredients, Staff. Shows Date, Reason, Ingredient, Quantity, Unit, Cost, Recorded By.
Calculated: Cost is
Round([Quantity] * [Cost per Unit], 2); Recorded By is[First Name] & " " & [Last Name].Only where
Days Ago Between 0 And 30.- Stock value by storage
What the stock in each store is worth at today's cost, for the month end stock count and the accounts.
From Ingredients. Shows Storage, Ingredients, Value at Cost, with totals.
Calculated: Value at Cost is
Round([In Stock] * [Cost per Unit], 2).
Forms
Every table has a form of the same name: Start Here, Business, Menu Categories, Menu Items, Suppliers, Ingredients, Recipe Lines, Staff, Payslips, Shifts, Orders, Order Lines, Purchases, Purchase Lines, Waste, Cash Ups. 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.
- Menu costing sheet
Every dish on the menu by section with its price, food cost, food cost percentage, gross profit and margin, and the average for each section. For the owner when pricing the menu.
From the query Menu costing, a group for each Category: Menu Item, Price, Food Cost, Food Cost %, Gross Profit, Gross Margin %, with the average of Food Cost %, average of Gross Profit.
- Recipe cards
A card for each dish listing its ingredients, the quantity of each and its cost, with the dish's total food cost. For the kitchen wall and for training.
From the query Recipe card, a group for each Menu Item: Line, Ingredient, Quantity, Unit, Line Cost, with the total of Line Cost.
- Menu
The menu by section with prices and diet marks, ready to check against the printed menu.
From the query Menu, a group for each Category: Menu Item, Diet, Price.
- Weekly takings and wages
One line a week: days open, bills, covers, takings, wages, what was left after wages and the wage share, with the totals. For the owner's weekly look at the business.
From the query Takings and wages by week: Week Beginning, Days Open, Orders, Covers, Takings, Wages, Left After Wages, Average Day Wage %, with the total of Takings, total of Wages, total of Left After Wages.
- Monthly takings
Takings, VAT, takings less VAT and wages month by month with the totals, for the accountant, the VAT return or the bank.
From the query Takings by month: Month, Days Open, Orders, Takings, VAT, Takings less VAT, Wages, Left After Wages, with the total of Takings, total of VAT, total of Takings less VAT, total of Wages, total of Left After Wages.
- Rota for the next 7 days
The coming week's shifts, a section for each day with its total hours, to pin up in the back.
From the query Rota for the next 7 days, a group for each Day: From, To, Name, Station, Paid Hours, Status, with the total of Paid Hours.
- Order list for suppliers
What to order today, a section for each supplier with its delivery days, the quantities and the estimated cost. For whoever phones the orders in.
From the query Ingredients to order, a group for each Supplier: Ingredient, Unit, In Stock, On Order, Reorder Level, Order, Bought As, Estimated Cost, with the total of Estimated Cost.
- Waste report
The last 30 days of waste grouped by reason, with the cost of each and in all. For the weekly kitchen meeting.
From the query Waste in the last 30 days, a group for each Reason: Date, Ingredient, Quantity, Unit, Cost, Recorded By, with the total of Cost.
- Bills to pay
Unpaid supplier bills grouped by supplier, with their invoice numbers, the days left to pay and the total owed to each.
From the query Bills to pay, a group for each Supplier: Purchase, Invoice Number, Received On, Days to Pay, Amount, with the total of Amount.
- 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.
Receipt
The till receipt for a bill, on an 80 mm roll: the cafe's name, address and VAT number, the receipt number, time, eat in or take away and the table, each item with its price and VAT rate, then the total, the net and the VAT within it, and how it was paid. Numbered RCP-2026-00042. Every bill on record prints, voided ones too, so choose the bill to print.
One for each record of Orders, on a till roll 80 mm wide paper.
Receipt {Number}
Date and time · Service · Table · Paid by
A line for each record of Order Lines: Qty, Item, Each, Price, VAT %, VAT
Total · Net of VAT · VAT included
You were served by {Staff.First Name}. Please keep this receipt as your proof of purchase.
VAT number {Business.Tax Number}. {Business.Document Footer}
Purchase order
The order sent to a supplier each morning from the order list: what to send, how much, at the last price paid, and the day to deliver, with the cafe's address to deliver to. Numbered PO-2026-0042, which the supplier quotes on the delivery note and the bill. Every purchase prints, delivered ones too, so choose the ones placed today.
One for each record of Purchases, on A4 paper.
Purchase order {Number}
To · Order number · Address · Order date · City · Deliver on · Postcode · For the attention of
A line for each record of Purchase Lines: Ingredient, Comes as, Quantity, Unit, Price per unit, Amount
Order total
Please deliver to {Business.Name}, {Business.Address}, {Business.City} {Business.Postcode} on {Due On}, before 7 in the morning if you can. Quote {Number} on your delivery note and your invoice. Prices are those we last paid; tell us before delivery if any has changed.
Questions about this order: {Business.Phone} or {Business.E-mail}.
Signed: Ordered by, Received by
Payslip
The weekly payslip for one person: the week, the hourly rate, each shift worked or on holiday with its hours and pay, and the gross pay. Numbered PAY-2026-0042. It shows gross pay only; tax, National Insurance, pension and other deductions are for the payroll to work out, and its statement of net pay goes with this one.
One for each record of Payslips, on A4 paper.
Payslip {Number}
Employee · Week beginning · Role · Week ending · Hourly rate · Paid on
A line for each record of Shifts: Date, Day, From, To, Break (min), Station, Status, Hours, Rate, Pay
Hours paid · Gross pay
Gross pay for the hours on the rota, holiday included at the usual rate and sick days unpaid here. Tax, National Insurance, pension and any other deductions are worked out by the payroll and shown on the payroll's own statement of net pay.
Back to the overview of Little Fig Cafe