Data Files samples / Shop
Larkspur Corner Store
Products, the till and its receipts, VAT, stock, supplier orders, deliveries, loyalty customers and running costs for a neighbourhood 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.
- Today's takings
What the till has taken today so far, by how people paid, and how many items went out. Run it at closing time to check against the cash drawer and the card terminal.
From Sales, Sale Lines. Shows Payment, Takings, Items, with totals.
Calculated: Takings is
Round([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2).Only where
Days Ago = 0andRefunded = No.- Daily takings
Takings and items sold on each of the last 30 days, the newest first, to spot a slow day.
From Sales, Sale Lines. Shows Day, Takings, Items, with totals.
Calculated: Day is
Format([When], "yyyy-mm-dd"); Takings isRound([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2).Only where
Days Ago Between 0 And 30andRefunded = No.- Monthly takings
Takings for each month of the year, refunds left out.
From Sales, Sale Lines. Shows Month, Takings, with totals.
Calculated: Month is
Format([When], "yyyy-mm"); Takings isRound([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2).Only where
Refunded = No.- Busy hours
How many sales the till rang up in each hour of the day over the last 90 days, to plan who works when.
From Sales. Shows Hour, Sales, with totals.
Calculated: Hour is
Mid([When], 12, 2).Only where
Days Ago Between 0 And 90.- Best sellers
The products that sold most in the last 30 days, with what they 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
Refunded = NoandDays Ago Between 0 And 30.- Profit by category
For each section of the shop, what it took, the VAT within that, and what it earned after the VAT and the cost of the goods, the best earner first.
From Sale Lines, Products, Categories, Sales. Shows Category, Takings, VAT, Profit, 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); Profit 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] * [Cost Price], 2).Only where
Refunded = No.- Takings by staff
What each person rang up over the last 30 days.
From Sales, Staff, Sale Lines. Shows Staff Member, Role, Takings, with totals.
Calculated: Staff Member is
[First Name] & " " & [Last Name]; Takings isRound([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2).Only where
Days Ago Between 0 And 30andRefunded = No.- Low stock
Products at or below their reorder level, by supplier, with the days each supplier delivers. This is the shopping list each supplier order is made from.
From Products, Suppliers. Shows Supplier, Delivers On, Product, In Stock, Reorder Level.
Only where
Spare <= 0andActive = Yes.- Stock value
What the stock on the shelves is worth by section, at cost and at sale price.
From Products, Categories. Shows Category, Items, At Cost, At Sale Price, with totals.
Calculated: At Cost is
Round([In Stock] * [Cost Price], 2); At Sale Price isRound([In Stock] * [Sale Price], 2).- Costs by month
What the shop paid out each month, by kind of cost.
From Expenses. Shows Month, Kind, Amount, with totals.
Calculated: Month is
Format([Date], "yyyy-mm").- Best customers
Loyalty customers by what they have spent, with their points.
From Customers, Sales, Sale Lines. Shows Customer, Spent, Points, with totals.
Calculated: Customer is
[First Name] & " " & [Last Name]; Spent isRound([Quantity] * [Unit Price] * (1 - Nz([Discount])), 2).Only where
Refunded = No.- Unpaid deliveries
Supplier invoices not yet paid, oldest first, with what each one comes to.
From Deliveries, Suppliers, Delivery Lines. Shows Delivery, Supplier, Received, Invoice Number, Owed, with totals.
Calculated: Owed is
Round([Quantity] * [Unit Cost], 2).Only where
Paid = No.- VAT on sales by month
For each month, the takings, the part at the standard rate, the VAT within it and the part at zero or exempt: the output VAT for the VAT return, refunds left out.
From Sales. Shows Month, Takings, At 20%, VAT, At 0% or Exempt, with totals.
Calculated: Month is
Format([When], "yyyy-mm"); At 0% or Exempt isRound([Total] - [Standard Rated], 2).Only where
Refunded = No.- 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([Quantity] * [Unit Price] * Nz([Discount]), 2).Only where
Days Ago Between 0 And 1.- Supplier orders, last 7 days
The orders sent to suppliers in the last week, the newest first, with when each is due and whether it came.
From Supplier Orders, Suppliers. Shows Number, Supplier, Ordered On, Delivery Due, Status.
Only where
Days Ago Between 0 And 6.
Forms
Every table has a form of the same name: Start Here, Business, Categories, Suppliers, Products, Staff, Customers, Sales, Sale Lines, Supplier Orders, Supplier Order Lines, Deliveries, Delivery Lines, Expenses. 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.
- Takings by month
One line a month with the takings and a total for the year.
From the query Monthly takings: Month, Takings, with the total of Takings.
- Takings, the last 30 days
Takings and items for each of the last 30 days, with the total and the average day.
From the query Daily takings: Day, Items, Takings, with the total of Takings, average of Takings.
- Profit by category
Takings, VAT and profit by section, with totals: the page to look at when deciding what to stock more of.
From the query Profit by category: Category, Takings, VAT, Profit, with the total of Takings, total of VAT, total of Profit.
- To order
What to order, on a page for each supplier, with what is left and the reorder level.
From the query Low stock, a group for each Supplier: Product, In Stock, Reorder Level.
- Stock take sheet
Every product by section with its barcode and what the database says is in stock, to count against.
From the table Products, a group for each Category: Name, Barcode, In stock, with the total of In Stock.
- Running costs
Each month's costs by kind, with a total for the month.
From the query Costs by month, a group for each Month: Kind, Amount, with the total of Amount.
- Deliveries to pay
Supplier invoices still to pay, by supplier, with a total for each.
From the query Unpaid deliveries, a group for each Supplier: Delivery, Received, Invoice Number, Owed, with the total of Owed.
- 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% or Exempt, 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.
Sale receipt
The till receipt on an 80 mm roll: the shop's name, address and VAT number, the receipt number, date, time and who served, each item with its price and VAT code, the total, the VAT included by rate, the cash tendered and change for cash sales, loyalty points earned, and the returns policy from the Business record. 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 · Loyalty card
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, E, O: sales at 0% or exempt · Loyalty points earned
VAT codes: S standard rate 20%, Z zero rate, E exempt, O outside the scope of VAT. VAT number {Business.Tax Number}.
{Business.Document Footer}
Supplier order
The order sent to a supplier: its number, the shop's account with them, the delivery day, each product with its barcode, quantity and cost, and the total before VAT. Print it from the Supplier Orders form for the order on screen, or e-mail it as a PDF.
One for each record of Supplier Orders, on A4 paper.
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 Supplier Order Lines: Barcode, Product, Quantity, Unit Cost, Total
Items · Total before VAT
Please deliver on {Delivery Due} to {Business.Name}, {Business.Address}, {Business.City} {Business.Postcode}, and quote {Number} on your invoice. If a line is out of stock, please tell us on the day rather than send a substitute.
Chilled and fresh lines are checked on arrival and will be refused with less than three days left on their date.
Back to the overview of Larkspur Corner Store