Data Files samples / Pharmacy counter

Tansy Row Pharmacy

Counter sales, stock by batch and expiry, pharmacist checks, prescription collections and home deliveries for an independent pharmacy.

Download 393 KB18 tables, 15 queries, 11 reports, 4 print templates and 21,941 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.

Supplier Orders.Supplier points at Suppliers
Supplier Orders.Ordered By points at Staff
Supplier Returns.Supplier points at Suppliers
Products.Category points at Categories
Products.Supplier points at Suppliers
Supplier Order Lines.Order points at Supplier Orders
Supplier Order Lines.Product points at Products
Batches.Product points at Products
Waste.Batch points at Batches
Waste.Return points at Supplier Returns
Sales.Served By points at Staff
Sale Lines.Sale points at Sales
Sale Lines.Product points at Products
Pharmacist Checks.Product points at Products
Pharmacist Checks.Pharmacist points at Staff
Pharmacist Checks.Sale points at Sales
Delivery Rounds.Driver points at Staff
Deliveries.Round points at Delivery Rounds
Deliveries.Customer points at Delivery Customers

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. (32 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 wholesalers and suppliers the pharmacy buys from, with when they take orders and whether they take back out-of-date stock. (3 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, unique	The supplier's name, printed on purchase orders and return notes.
Kind	Short text	Wholesaler for medicines, or Toiletries for the supplier of toiletries, baby care and period care.
Account No	Short text	The pharmacy's account number with the supplier, quoted on every order and return.
Address	Short text	The street address.
City	Short text	The town or city.
Postcode	Short text	The postcode.
Phone	Short text	The order line.
E-mail	Short text	Where orders and returns are sent.
Order Days	Short text	The days the pharmacy orders from this supplier, in words.
Lead Days	Whole number	How many days after the order the goods arrive.
Payment Days	Whole number	How many days after the supplier's invoice payment is due.
Takes Returns	Yes/No	Ticked when the supplier takes back out-of-date stock for credit. Stock from the others is written off for disposal.
Notes	Long text	Anything worth knowing about ordering from them.

Staff

Everyone who works at the pharmacy: the pharmacists, the technician, the counter assistants and the drivers. (7 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 till receipts.
Last Name	Short text, required	Their family name.
Role	Short text	Pharmacist, Pharmacy technician, Counter assistant or Driver.
Phone	Short text	Their phone number.
E-mail	Short text	Their work e-mail address.
Started	Date	The day they started.

Supplier Orders

The orders sent to suppliers, one record each, with their lines in Supplier Order Lines. (388 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, printed on the purchase order and quoted by the supplier: PO-2026-0042, a new run each year.
Supplier	Points at Suppliers, required	Who the order went to.
Ordered By	Points at Staff	Who placed it.
Ordered On	Date	The day it was sent.
Expected	Date	The day the goods should arrive.
Received On	Date	The day they arrived; empty until then.
Status	Short text	Submitted until the goods arrive, then Received.

Supplier Returns

Out-of-date stock sent back to a wholesaler for credit, one record for each expiry return note. Its lines are the Waste records that point at it. (17 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 return's number, printed on the note: RTN-2026-0007.
Supplier	Points at Suppliers, required	The wholesaler the stock goes back to.
Raised On	Date	The day the note was made out.
Collected On	Date	The day the driver took it away; empty until then.
Status	Short text	Awaiting collection, Awaiting credit, or Credited.
Credit Claimed	Currency	The value of the stock returned, at cost: the total of the note.
Credit Note	Short text	The supplier's credit note reference, once it arrives.
Credited On	Date	The day the credit arrived.

Products

Everything on the shelves, one record for each line, under its generic name. What is in stock is the sum of its batches. (76 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 pharmacy's own code for the line. Each is used once.
Name	Short text, required	The product's generic name and pack, as the till and the receipt show it.
Category	Points at Categories	The group it is sold in, which gives its VAT rate.
Supplier	Points at Suppliers	Who it is ordered from.
Pack	Short text	The pack size or contents.
Cost Price	Currency	What one pack costs from the supplier, before VAT.
Retail Price	Currency	The shelf price of one pack, VAT included.
Units in Stock	Whole number	Packs on the shelf now: the sum of Quantity Left over its batches.
Reorder Level	Whole number	When stock falls to this, it goes on the next order.
Reorder Quantity	Whole number	How many packs to order each time.
Pharmacist Check	Yes/No	Ticked for a pharmacy-only line: the pharmacist speaks to the customer before it is sold, and the check is recorded.
Max per Sale	Whole number	The most packs sold in one sale, where the pharmacy keeps a limit. Empty for no limit.
Discontinued	Yes/No	Ticked for a line no longer stocked.

Supplier Order Lines

The products on each supplier order. (1,114 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	Points at Supplier Orders, required	The order the line belongs to.
Product	Points at Products, required	What is ordered.
Quantity	Whole number	How many packs.
Unit Cost	Currency	The cost of one pack on this order.

Batches

Stock as it arrived: one record for each delivery of a product, with its batch number and expiry date, and how much of it is left. (1,219 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	The product.
Batch No	Short text	The batch number printed on the pack.
Expiry Date	Date	The date printed on the pack after which it must not be sold.
Received On	Date	The day it arrived.
Quantity Received	Whole number	How many packs arrived.
Quantity Left	Whole number	How many of them are still on the shelf. A sale takes from the batch that expires first.

Waste

Stock written off: out of date, or damaged. Out-of-date stock from a supplier that takes returns points at the return note it went back on. (61 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.
Batch	Points at Batches, required	The batch written off, which gives the product and its expiry date.
Return	Points at Supplier Returns	The expiry return note it went back to the supplier on. Empty when it was disposed of.
Written Off On	Date	The day it came off the shelf.
Quantity	Whole number	How many packs.
Unit Cost	Currency	The cost of one pack, for the value of the loss or the credit.
Reason	Short text	Out of date, returned to supplier; Out of date, for disposal; Damaged in delivery, returned to supplier; or Damaged in store.

Sales

The till: one record for each sale, with its lines in Sale Lines. Each prints as a till receipt. (5,214 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, in time order, a new run each year: RCP-2026-00042.
When	Date and time	The date and time of the sale.
Till	Short text	Which till took it.
Served By	Points at Staff	Who served the customer.
Payment	Short text	Card or Cash.
Total	Currency	What the customer paid for the goods, VAT included: the sum of the lines.
VAT	Currency	The VAT within the total, worked out on each line and added up.
Paid	Currency	What the customer handed over: the total by card, or the cash given.
Change	Currency	The change given from cash.

Sale Lines

The products on each sale. (7,879 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 sale the line belongs to.
Product	Points at Products, required	What was sold.
Quantity	Whole number	How many packs.
Unit Price	Currency	The shelf price of one pack at the time, VAT included.
Tax Rate	Number	The VAT rate of the line, taken from the product's group when it was sold.
Unit Cost	Currency	What one pack cost the pharmacy, kept for the margin.
Pharmacist Check	Yes/No	Ticked for a pharmacy-only line, so the receipt marks it.

Pharmacist Checks

A record of every request for a pharmacy-only line: the pharmacist spoke to the customer and decided whether to sell. The words are short and practical, never clinical. (818 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.
When	Date and time	The date and time of the check.
Product	Points at Products, required	The line asked for.
Quantity	Whole number	How many packs were asked for.
Pharmacist	Points at Staff, required	The pharmacist who made the check.
Sale	Points at Sales	The sale it was sold on. Empty when it was not sold.
For Whom	Short text	Self, or Someone at home.
Outcome	Short text	Sold, or Not sold.
Note	Short text	A few plain words where there is something to say, such as Chose a different product.

Prescriptions

A minimal log of prescriptions waiting to be collected or delivered: initials, a reference and a count of items. It holds nothing clinical; the dispensary keeps its own records. (3,244 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.
Reference	Short text, unique	The pharmacy's reference for the prescription bag: RX-2026-00042.
Initials	Short text	The customer's initials, to find the bag on the shelf.
Received On	Date	The day the prescription came in.
Items	Whole number	How many items are in the bag.
Ready On	Date	The day the bag was ready.
Collected On	Date	The day it was collected or delivered; empty while it waits.
Status	Short text	Being made up, Ready, Collected, Delivered, or Not collected (back to stock after 28 days).
Shelf	Short text	Where the bag waits: the letter of the family name and a shelf number.
For Delivery	Yes/No	Ticked when it goes out on a delivery round instead of being collected.

Delivery Customers

Customers who cannot get to the pharmacy and have their prescriptions delivered, with the day their round comes. (42 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.
Customer No	Short text, unique	The pharmacy's number for the customer: DC-0012.
First Name	Short text	Their first name.
Last Name	Short text, required	Their family name.
Address	Short text	Where to deliver.
City	Short text	The town.
Postcode	Short text	The postcode, which the driver plans the round by.
Phone	Short text	The number to ring before or if no one answers.
Area	Short text	North or South: which weekday round they are on.
Round Day	Short text	The day of the week their delivery comes.
Every Weeks	Whole number	How often: 1 every week, 2 every fortnight, 4 every four weeks.
Access Notes	Short text	What the driver should know at the door, in plain words.
Active	Yes/No	Ticked while they have deliveries.
Since	Date	The day deliveries began.

Delivery Rounds

One record for each driver's round on each day, with its stops in Deliveries. Each prints as a round sheet. (546 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 round's number: RND-2026-0123.
Delivery Date	Date	The day of the round.
Driver	Points at Staff, required	Who drives it.
Area	Short text	North or South on a weekday, All at the weekend.
Stops	Whole number	How many stops it has.
Bags	Whole number	How many bags go out on it.
Status	Short text	Planned, or Done once the driver is back.

Deliveries

Each stop on a round: who, which prescription bag, and what happened. (1,270 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.
Round	Points at Delivery Rounds, required	The round the stop is on.
Stop	Whole number	Its place in the round.
Customer	Points at Delivery Customers, required	Who it is for.
Prescription Ref	Short text	The reference of the bag, as in Prescriptions.
Bags	Whole number	How many bags.
Status	Short text	Planned, Delivered, or Not in, card left (it goes again the next day).
Delivered At	Date and time	When it was handed over.
Received By	Short text	Who took it: Customer, Carer or Family member.
Notes	Short text	Anything the driver should know about this stop.

Next: Queries, forms, reports and print templates