Data Files samples / Food and beverages
Bramblegate Wine and Deli
Batches with their dates, markdowns, write-offs, the till, age checks and refusals, suppliers and purchase orders for a food and drink shop.
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.
- Expiring in the next 7 days
Batches still on the shelf whose date is in the next week, by category and date, with how many days are left, what is left and any reduced price. This is the list for the morning date check.
From Batches, Products, Categories. Shows Category, Expiry Date, Product, Batch Code, Date Kind, Days Left, Remaining, Sale Price, Marked Down To.
Calculated: Days Left is
DateDiff("d", Date(), [Expiry Date]).Only where
Days Left Between 0 And 7andRemaining > 0.- Marked down, last 30 days
Every markdown of the last month, the newest first, with the price before and after and how many labels were printed.
From Markdowns, Batches, Products. Shows Date, Number, Product, Batch Code, Expiry Date, Was, Now, Labels, Reason.
Only where
Days Ago Between 0 And 30.- Written off by month
Units and cost written off each month, by reason: out of date, broken or damaged.
From Write-offs. Shows Month, Reason, Units, Cost, with totals.
Calculated: Month is
Format([Date], "yyyy-mm").- Write-offs by product
Which lines were written off most over the last 90 days, by cost: the ones to order less of.
From Write-offs, Batches, Products, Categories. Shows Category, Product, Units, Cost, with totals.
Only where
Days Ago Between 0 And 90.- Refusals, last 30 days
The refusals of the last month, the newest first, with what was asked for, why and who refused.
From Refusals, Products, Staff. Shows When, Number, Asked For, Reason, Approx Age, ID Shown, Refused By.
Calculated: Refused By is
[First Name] & " " & [Last Name].Only where
Days Ago Between 0 And 30.- Age checks by month
How many alcohol sales each month were to customers who looked over 25 and how many had their ID checked.
From Sales. Shows Month, Age Check, Sales, with totals.
Calculated: Month is
Format([When], "yyyy-mm").Only where
Age Check <> "Not needed".- Best sellers
What sold most in the last 30 days, with what it took.
From Sale Lines, Products, Sales. Shows Product, Sold, Takings, with totals.
Calculated: Takings is
Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2).Only where
Days Ago Between 0 And 30.- Margin by category
For each category, the takings, the VAT within them, what the goods cost by batch and the margin left, the best earner first. Markdowns lower the margin; write-offs are counted in their own queries.
From Sale Lines, Products, Categories. Shows Category, Takings, VAT, Cost, Margin, with totals.
Calculated: Takings is
Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2); VAT isRound(Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2) - Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2) / (1 + [Sale Lines].[Tax Rate]), 2); Cost isRound([Quantity] * [Sale Lines].[Unit Cost], 2); Margin isRound(Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2) - Round(Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2) - Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2) / (1 + [Sale Lines].[Tax Rate]), 2) - [Quantity] * [Sale Lines].[Unit Cost], 2).- Stock to reorder
Lines at or below their reorder level, by supplier, with the pack size they are ordered in.
From Products, Suppliers. Shows Supplier, Code, Product, In Stock, Reorder Level, Pack Size.
Only where
Spare <= 0andActive = Yes.- Stock by batch
Every batch with stock left, by category and product, earliest date first, with its value at cost.
From Batches, Products, Categories. Shows Category, Product, Batch Code, Received, Expiry Date, Remaining, Value at Cost.
Calculated: Value at Cost is
Round([Remaining] * [Batches].[Unit Cost], 2).Only where
Remaining > 0.- Daily takings
Sales and takings on each of the last 30 days, the newest first.
From Sales. Shows Day, Sales, Takings, with totals.
Calculated: Day is
Format([When], "yyyy-mm-dd").Only where
Days Ago Between 0 And 30.- Monthly takings
Sales and takings each month.
From Sales. Shows Month, Sales, Takings, with totals.
Calculated: Month is
Format([When], "yyyy-mm").- VAT on sales by month
For each month, the takings, the part at 20%, the VAT within it and the part at 0%: the output VAT for the VAT return.
From Sales. Shows Month, Takings, At 20%, VAT, At 0%, with totals.
Calculated: Month is
Format([When], "yyyy-mm"); At 0% isRound([Total] - [Standard Rated], 2).- Deliveries to pay
Deliveries in whose supplier invoice is not yet paid, by supplier, with what each comes to before VAT.
From Purchase Orders, Suppliers, Purchase Lines. Shows Number, Supplier, Received On, Supplier Invoice, Owed, with totals.
Calculated: Owed is
Round([Quantity] * [Unit Cost], 2).Only where
Status = ReceivedandPaid = No.- Purchase orders open
Orders sent and not yet delivered, by the day they are expected.
From Purchase Orders, Suppliers. Shows Number, Supplier, Ordered On, Expected.
Only where
Status = Submitted.- Receipt totals
Today's and yesterday's receipts with their total, VAT, standard-rated part and saving worked out from their lines. When you enter a sale by hand, read its figures here and type them into the sale.
From Sales, Sale Lines. Shows Receipt, When, Total, VAT, Standard Rated, Saved, with totals.
Calculated: Total is
Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2); VAT isRound(Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2) - Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2) / (1 + [Sale Lines].[Tax Rate]), 2); Standard Rated isIIf([Sale Lines].[Tax Rate] > 0, Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2), 0); Saved isRound(Round([Quantity] * [Full Price], 2) - Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2), 2).Only where
Days Ago Between 0 And 1.
Forms
Every table has a form of the same name: Start Here, Business, Suppliers, Staff, Categories, Products, Purchase Orders, Purchase Lines, Batches, Sales, Sale Lines, Date Checks, Markdowns, Write-offs, Refusal Registers, Refusals. 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.
- Use first: dates in the next 7 days
The batches with a date in the next seven days, on a page for each category, to walk the shelves with each morning.
From the query Expiring in the next 7 days, a group for each Category: Product, Batch Code, Date Kind, Expiry Date, Days Left, Remaining, Marked Down To, with the total of Remaining.
- Write-offs by month
Each month's write-offs by reason, with the units and their cost, and the totals.
From the query Written off by month, a group for each Month: Reason, Units, Cost, with the total of Units, total of Cost.
- Margin by category
Takings, VAT, cost and margin by category, with totals.
From the query Margin by category: Category, Takings, VAT, Cost, Margin, with the total of Takings, total of VAT, total of Cost, total of Margin.
- Stock to reorder
What is low, on a page for each supplier.
From the query Stock to reorder, a group for each Supplier: Code, Product, In Stock, Reorder Level, Pack Size.
- Takings by month
Sales and takings month by month, with the totals.
From the query Monthly takings: Month, Sales, Takings, with the total of Sales, total of Takings.
- Stock by batch
Every batch in stock by category, with its date and value at cost: the stock take sheet for a shop that sells by date.
From the query Stock by batch, a group for each Category: Product, Batch Code, Expiry Date, Remaining, Value at Cost, with the total of Value at Cost.
- VAT on sales, by month
The VAT on sales month by month, for the accountant or the VAT return.
From the query VAT on sales by month: Month, Takings, At 20%, VAT, At 0%, with the total of Takings, total of VAT.
- 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.
Till receipt
The receipt on an 80 mm till roll: the shop's name, address and VAT number, the receipt number, time and who served, each item with its price and VAT code, the total, what was saved, the cash tendered and change, the VAT included by rate, the age check, and the returns policy. Print it from the Sales form for the sale on screen.
One for each record of Sales, on a till roll 80 mm wide paper.
Receipt {Number}
Date and time · Served by · Paid by · Age check
A line for each record of Sale Lines: Item, Qty, Price, Amount, VAT
Total · You saved · Cash tendered · Change · S: sales at 20% VAT · VAT at 20% included · Z: sales at 0% VAT
Mixed cases: 10% off six or more bottles of wine, and 10% off twelve or more beers and ciders. Reduced items are sold as seen. VAT number {Business.Tax Number}.
{Business.Document Footer}
Purchase order
The order sent to a supplier: the shop's account with them, the delivery day, each product with its code, unit, quantity and cost, the total before VAT, and the shop's rules for dates and batch codes on what arrives. Print it from the Purchase Orders form for the order on screen.
One for each record of Purchase Orders, on A4 paper.
Purchase order {Number}
To · Order number · For the attention of · Date · Address · Deliver on · City · Our account · Postcode · Ordered by
A line for each record of Purchase Lines: Code, Product, Unit, Quantity, Unit Cost, Total
Units · Total before VAT
Please deliver on {Expected} to {Business.Name}, {Business.Address}, {Business.City} {Business.Postcode}, and quote {Number} on your delivery note and invoice.
Every case and pack must carry its batch code and its use-by or best-before date. Chilled and deli lines must arrive with at least two thirds of their life left, or they will be refused at the door. All alcohol must be duty paid.
Markdown label sheet
The sheet from each morning's date check: what to reduce, with the label number, batch, date, the old and new price and how many labels to print, then what was taken off sale and what it cost, signed by whoever did the check. Print it from the Date Checks form for the day on screen.
One for each record of Date Checks, on A4 paper.
Date check {Number}
Date · Checked by · Fridges
Reduce to clear: print a label for each, stick it over the shelf price on the pack, and put the packs at the front.
A line for each record of Markdowns: Label, Product, Batch, Date kind, Date, Was, Now, Labels
Taken off sale: past their date, broken or damaged. Put them in the waste bin, not back on the shelf.
A line for each record of Write-offs: Product, Batch, Date, Reason, Units, Cost
Labels to print · Units taken off sale · Cost written off
Signed: Checked by, Manager
Reduced label
The yellow-sticker label for one markdown, on a 58 mm label roll: the product, its date and batch, the old price and the reduced price. Print it from the Markdowns form, once for each pack.
One for each record of Markdowns, on a till roll 58 mm wide paper.
REDUCED
Product · Date kind · Date · Batch
Was · Now
Label {Number}, reduced on {Date}.
Refusals register
The month's refusals register, kept for the licensing authority: every refusal with its time, what was asked for, why, the ID shown and who refused, and the review signature of the personal licence holder. Print it from the Refusal Registers form for the month on screen.
One for each record of Refusal Registers, on A4 paper.
Refusals register {Month}
Month · Refusals · Reviewed by · Reviewed on
The shop asks anyone who looks under 25 to show photo ID with a date of birth: a photo driving licence, a passport or a proof-of-age card. When none is shown, when the ID does not fit the person, when the drink seems to be bought for someone under 18, or when the customer is drunk, the sale is refused and written down here at the time.
A line for each record of Refusals: No, Date and time, Asked for, Reason, Age (about), ID shown, What happened, Refused by
Refusals this month
Kept on the premises for the licensing authority, trading standards and the police to see on request.
Signed: Reviewed by the personal licence holder, Date
Back to the overview of Bramblegate Wine and Deli