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.

Download 662 KB16 tables, 16 queries, 8 reports, 5 print templates and 40,106 invented records, dated back from 2026-10-22.

How the tables are related

A line runs from a field that points at another table's record to that table. Data Files keeps a record from pointing at one that is not there, and a query with both tables joins them by it.

Products.Category points at Categories
Products.Supplier points at Suppliers
Purchase Orders.Supplier points at Suppliers
Purchase Orders.Staff points at Staff
Purchase Lines.Purchase Order points at Purchase Orders
Purchase Lines.Product points at Products
Batches.Product points at Products
Batches.Purchase Order points at Purchase Orders
Sales.Staff points at Staff
Sale Lines.Sale points at Sales
Sale Lines.Product points at Products
Sale Lines.Batch points at Batches
Date Checks.Staff points at Staff
Markdowns.Check points at Date Checks
Markdowns.Batch points at Batches
Write-offs.Check points at Date Checks
Write-offs.Batch points at Batches
Write-offs.Staff points at Staff
Refusal Registers.Reviewed By points at Staff
Refusals.Register points at Refusal Registers
Refusals.Staff points at Staff
Refusals.Product points at Products

Start Here

The guide to this database: the everyday tasks a step at a time, each step saying which table, form, query, report or print template to open. Open its report, Start Here, to read or print it. Delete the table when you no longer need it; nothing else uses it. (31 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
Order	Whole number	The order the steps are read in.
Task	Short text, required	The everyday task the step belongs to.
Step	Whole number	The step's number within its task.
What To Do	Long text	What to open and what to do there.

Business

The business's own details, in one record. Every document it prints, an invoice, a receipt, a statement or a letter, takes its name, address and payment details from here, so they are changed in one place. (1 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
Name	Short text, required	The name printed at the top of every document.
Address	Short text	The street address.
City	Short text	The town or city.
Postcode	Short text	The postcode or ZIP code.
Country	Short text	The country.
Phone	Short text	The phone number customers call.
E-mail	Short text	The address customers write to.
Website	Short text	The website, if there is one.
Tax Number	Short text	The VAT or sales tax registration number, printed on invoices. The sample has a placeholder.
Company Number	Short text	The registered company number, where the business is a company. The sample has a placeholder.
Bank Details	Short text	Where customers pay by transfer: the bank, account name and number. The sample has a placeholder.
Payment Terms	Short text	The words printed under an invoice's total, such as "Payment within 30 days".
Tax Rate	Number	The usual rate of VAT or sales tax as a fraction: 0.2 is twenty percent. Lines may carry a rate of their own.
Currency	Short text	The currency the business trades in, as its three-letter code.
Document Footer	Long text	A line or two at the foot of every document: thanks, opening hours, returns policy.

Suppliers

The merchants, brewers, bakers and wholesalers the shop buys from. (6 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
Name	Short text, required	The supplier's name.
Contact	Short text	The person you deal with there.
Address	Short text	The supplier's street address, printed on purchase orders.
City	Short text	The town or city.
Postcode	Short text	The postcode.
Phone	Short text	Their phone number.
E-mail	Short text	Where orders go.
Account Number	Short text	The shop's account number with the supplier, quoted on every order.
Delivers On	Short text	The days the supplier delivers.
Lead Days	Whole number	How many days before a delivery the order must reach them: 1 means the day before.

Staff

The people who work in the shop, on the till and on the deli counter. (5 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
First Name	Short text	Their first name, printed on the receipts they ring up.
Last Name	Short text, required	Their family name.
Role	Short text	Owner, Deli Manager, Shop Assistant or Weekend Assistant.
Phone	Short text	A number to reach them.
Started	Date	The day they started.
Personal Licence	Yes/No	Ticked for someone who holds a personal licence to sell alcohol; the owner is the premises supervisor.
Age Check Training	Date	The day they were last trained to ask for ID and to refuse a sale. Train everyone at least once a year.

Products

Everything the shop sells, one record for each line. (65 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
Code	Short text, unique	The shop's own code for the line. Each code is used once.
Name	Short text, required	The product as it shows on the till, the receipt and the labels.
Category	Points at Categories	The group it is sold in.
Supplier	Points at Suppliers	Who you buy it from.
Unit	Short text	How it is sold, such as a 75 cl bottle or a 200 g pack.
Date Kind	Short text	Use by for chilled food that must not be sold after its date, Best before for food that keeps, None for wine and spirits.
Shelf Life	Whole number	About how many days a new delivery lasts before its date; empty for wine and spirits.
Cost Price	Currency	What one costs you, before VAT.
Sale Price	Currency	The shelf price, VAT included.
In Stock	Whole number	How many are in the shop now: all its batches added up.
Reorder Level	Whole number	When stock falls to this number or below, it shows in Stock to reorder.
Pack Size	Whole number	How many come in the supplier's case or pack; orders are in whole packs.
Active	Yes/No	Untick a line you no longer sell; old sales keep it.

Purchase Orders

The orders sent to suppliers, and the delivery that answers each. (614 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
Number	Short text, unique	The order's number, given in date order and once only: PO-2026-0042, a new run each year.
Supplier	Points at Suppliers, required	Who the order goes to.
Staff	Points at Staff	Who placed it.
Ordered On	Date	The day it was sent.
Expected	Date	The delivery day it was ordered for.
Received On	Date	The day the goods came; empty until then.
Status	Short text	Submitted (not delivered yet) or Received.
Supplier Invoice	Short text	The number on the supplier's invoice that came with the goods.
Paid	Yes/No	Ticked once that invoice is paid.

Purchase Lines

The products on each purchase order. (3,989 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
Purchase Order	Points at Purchase Orders, required	The order the line belongs to.
Product	Points at Products, required	What was ordered.
Quantity	Whole number	How many units, in whole packs.
Unit Cost	Currency	What one unit costs, before VAT.

Batches

Stock as it really sits on the shelf: each delivery of each product is a batch with its own code and date. Stock by batch is what tells you what to sell first. (3,973 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
Product	Points at Products, required	What the batch is.
Purchase Order	Points at Purchase Orders	The order it came in on.
Batch Code	Short text	The lot code printed on the pack, for tracing a batch if a supplier recalls it.
Received	Date	The day it came in.
Expiry Date	Date	The use-by or best-before date on the packs; empty for wine and spirits.
Quantity	Whole number	How many came in.
Unit Cost	Currency	What one cost on that delivery.
Remaining	Whole number	How many are left: what came in, less what was sold and written off.
Marked Down To	Currency	The reduced price on the batch's labels, once it has been marked down; empty while it sells at the shelf price.

Sales

Each time the till is rung up: one record per receipt, with the totals the receipt prints. (9,412 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
Number	Short text, unique	The receipt number, given in time order and once only: RCP-2026-01234, a new run each year.
When	Date and time	The date and time of the sale.
Staff	Points at Staff	Who was on the till.
Payment	Short text	Card, Cash or Mobile.
Total	Currency	What the customer paid, VAT included.
VAT	Currency	The VAT inside the total: each line's VAT rounded to the penny, then added up.
Standard Rated	Currency	The part of the total, VAT included, at the standard rate. The rest is zero-rated.
Saved	Currency	What the customer saved on reduced items and mixed cases.
Tendered	Currency	For a cash sale, the cash handed over; empty for card and mobile.
Change	Currency	For a cash sale, the change given back.
Age Check	Short text	For a sale with alcohol: Looked over 25 (no ID needed) or ID checked. Not needed for a sale with none.
ID Seen	Short text	When ID was checked, what was shown: a driving licence, a passport or a proof-of-age card.

Sale Lines

The items on each receipt, each taken from one batch. (20,129 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
Sale	Points at Sales, required	The receipt the line belongs to.
Product	Points at Products, required	What was sold.
Batch	Points at Batches	The batch it came from: the one with the earliest date.
Quantity	Whole number	How many.
Full Price	Currency	The shelf price at the time.
Unit Price	Currency	The price charged for one: the shelf price, or the reduced price of a marked-down batch.
Discount	Number	The mixed-case discount as a fraction: 0.1 is ten percent off. Never on top of a reduced price.
Tax Rate	Number	The VAT rate of the line, copied from its category.
Unit Cost	Currency	What one cost, from its batch, for the margin.

Date Checks

The check made each morning before opening: what is near its date is marked down, what has passed it is taken off sale, and the fridges are read. (354 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
Number	Short text, unique	The check's number, one a day: DC-2026-0042, a new run each year.
Date	Date	The day of the check.
Staff	Points at Staff	Who did it.
Fridge Temperatures	Short text	What the chiller and the deli counter read, in degrees: both should be 5 or below.

Markdowns

Each time a batch is reduced: the yellow-sticker price and the labels printed for it. (1,084 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
Number	Short text, unique	The label's number, printed on it: MD-2026-00042, a new run each year.
Check	Points at Date Checks	The date check it was made at.
Batch	Points at Batches, required	The batch reduced.
Date	Date	The day it was reduced.
Was	Currency	The price before.
Now	Currency	The reduced price.
Labels	Whole number	How many labels to print: one for each pack left in the batch.
Reason	Short text	One day left, Last day (a use-by or bread date that is today) or Short date (food that keeps, within two weeks of its date).

Write-offs

Stock taken off sale and thrown away or returned, with what it cost. (324 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
Check	Points at Date Checks	The date check that found it; empty for a breakage during the day.
Batch	Points at Batches, required	The batch it came from.
Date	Date	The day it was written off.
Quantity	Whole number	How many units.
Reason	Short text	Out of date, Broken or Damaged.
Cost	Currency	What the units cost: the quantity at the batch's unit cost.
Staff	Points at Staff	Who wrote it off.

Refusal Registers

The refusals register, one record a month, reviewed and signed by the personal licence holder when the month ends. (12 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
Month	Short text, required, unique	The month the register covers, as 2026-10. Each month is used once.
Refusals Logged	Whole number	How many refusals the month has.
Reviewed By	Points at Staff	Who reviewed it: a personal licence holder.
Reviewed On	Date	The day it was reviewed; empty for the month still running.

Refusals

Each sale of alcohol refused, written down at the time, as a licensed shop keeps them. (99 records in the sample.)

Field	Type	What it holds
ID	AutoNumber	The number Data Files gives each record. Other tables point at a record by it.
Number	Short text, unique	The refusal's number: REF-2026-0042, a new run each year.
Register	Points at Refusal Registers	The month's register it is written in.
When	Date and time	The date and time.
Staff	Points at Staff	Who refused the sale.
Product	Points at Products	What the customer tried to buy.
Reason	Short text	No ID shown, ID not acceptable, Under 18 on ID, Suspected proxy sale (buying for someone under age) or Customer drunk.
Approx Age	Whole number	How old the customer looked, in years.
ID Shown	Short text	What ID was offered, if any.
Description	Short text	What happened, in a few words, so the person could be recognised if they come back. No names.

Next: Queries, forms, reports and print templates