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.

Rutba Data Files with Tansy Row Pharmacy, the pharmacy counter sample, open at its print template Till receipt: a page laid out with the business's own name and address at the head and one of its records filled in, beside the list of the database's tables and how many records each holds — Tansy Row Pharmacy in Rutba Data Files, made with its invented records and open at its print template Till receipt.

Overview

For the shop side of an independent pharmacy: the medicines and toiletries sold over the counter, the stock behind them kept batch by batch with expiry dates, the orders to the wholesalers, and the deliveries made to customers who cannot get out. Tansy Row Pharmacy is invented: two pharmacists, a pharmacy technician, two counter assistants and two delivery drivers, open every day but Christmas Day.

The records cover the year up to the day the file is made, and the next two days of deliveries. Cough and cold remedies sell in winter, hay fever tablets and sun cream in summer. A sale takes the batch nearest its expiry date first. Each Monday the technician takes off the shelf anything that runs out of date within the week, and on the first Monday of the month it goes back to the wholesaler on an expiry return note for credit; what the toiletries supplier will not take back is written off for disposal. Whatever has fallen to its reorder level is ordered: from the main wholesaler every weekday, from the second on Tuesdays and Thursdays, and from the toiletries supplier on Mondays.

Some lines are sold only after a word with the pharmacist. Each of those sales has a record of the check: who it was for, which pharmacist, and whether it was sold. A few are not. The prescription log is kept deliberately small: initials, a reference, how many items, and whether they were collected, so the counter can see what is waiting on the shelf. It holds no medicines, doses or anything clinical, and the dispensary's own records belong in its own system.

Every name is generic and every person is made up. Nothing here is advice about any medicine, and the pharmacy-only lines and pack limits are only examples: check the rules where you work.

In Rutba Data Files, choose Pharmacy counter on the start screen, then With sample records to look around, or Empty for the same design with nothing in it.

It suits, with small changes

A pharmacy's counter and shop
A health food or vitamin shop that tracks expiry dates
A chemist and toiletries shop
An optician's or audiologist's shop that sells products as well
A veterinary practice's counter, with pet medicines and food
A farm or garden shop that keeps batches with dates

Read on

Tutorial: the everyday tasks: 7 everyday tasks, step by step, each naming what to open.
Tables, fields and relationships: every field of 18 tables, and how they are related.
Queries, forms, reports and print templates: what each one shows, its criteria and calculated columns, and when to print it.

Things you should know

Everything in a sample's records is invented: the business, the people, their addresses, e-mail addresses on example domains that reach nobody, and phone numbers from the ranges kept for drama and fiction. Tax, company and bank numbers are placeholders of zeros.
The dates count back from the day the file was made, and some run on into the weeks after it, as real bookings and due dates do. Made from the Data Files start screen, a sample is dated from that day. Queries that compare with today (overdue, due this week) show more as a downloaded file gets older.
To start empty, choose the same business on the start screen and then Empty: the same tables, queries, forms, reports and print templates, with nothing in them but the Start Here guide.
Your records live in one file on your computer. Keep copies: File, Save As makes one, and a copy on another drive or in your own cloud storage keeps it safe if this computer is lost. Close the database before copying the file by hand.
Every change to the design (a new field, a renamed table) is a numbered step that Data Files keeps, so you can see what was changed and when.
Units in Stock is the sum of Quantity Left over a product's batches. Data Files does not work it out by itself: when you sell, receive or write off stock, change the batch and the product together.
A sale takes from the batch that runs out first. Keep the shelf in that order too, so the oldest packs go first.
Prices in Products and Sale Lines include VAT, as shelf prices do. The VAT on a receipt is worked out within each line, rounded to the penny, and added up. Period products carry no VAT and stop smoking products 5 percent in the UK; check the rates where you are.
The Prescriptions table is a collection log, not a dispensary record. Keep it to initials, a reference and a count of items, and keep anything clinical out of this file.
Which lines need a pharmacist's check, and any limit on packs in one sale, are set on each product. The sample's choices are examples only; follow the rules where you work.
Each print template prints every record of its table: every sale has a receipt and every round a sheet. Print the one on screen from its form, or choose the records to print.
The dispensary's own income, from prescriptions, is not in this file: the takings are the counter's.

Starting with your own records

Start with Business: the pharmacy's name, address, VAT number and the words at the foot of a receipt. Then Suppliers, Categories with their VAT rates, and Staff. Add Products with their supplier, prices and reorder levels, ticking Pharmacist Check where it applies. Enter the stock you hold as one Batch per product with its expiry date, and set Units in Stock to match. Add Delivery Customers if you deliver. Sales, Pharmacist Checks, Prescriptions, Supplier Orders, Waste and rounds come in as you trade.

Making it yours

Every change to the design is a numbered step in Data Files, so a change can be seen and undone.

Take a deposit or keep an account for a care home: add an Accounts table and an Account field on Sales, and a statement template on Accounts with its sales as lines.
Add a second till: type Till 2 on its sales, and group the Takings by day query by Till as well.
Run promotions: add a Promotions table (product, price, from, to) and use its price on the sale line while it runs.
Track stock in a back room: add a Location field to Batches, Shelf or Store room, and show it on the Expiry check report.
Count stock: add a Stock Counts table (product, date, counted) and a query comparing it with Units in Stock.
Keep a fridge temperature log: add a Fridge Log table with the date, the reading and who took it, and a query for readings outside your range.

Questions people ask

How do I see what is going out of date?: Open the Expiring in the next 30 days query, or print the Expiry check report. It lists batches with packs left and fewer than 31 days to their expiry date, by supplier.
Why does a product have more than one batch?: Each delivery is a batch of its own, with its own expiry date. Units in Stock is what is left over all of them.
How do I reprint a customer's receipt?: Open the Sales form, find the sale by its Number or its time, and print the Till receipt template for it.
Where is the VAT on a receipt?: Shelf prices include VAT, so the receipt shows the total paid and, below it, the VAT included in that total. The Takings by category query shows the same split for the year.
What happens to stock the toiletries supplier will not take back?: It is written off in Waste with the reason Out of date, for disposal, and no return note. Waste by month shows the cost.
Can I add a customer who collects for someone else?: The collection log only keeps initials. If you need more, keep it in the dispensary's own system rather than here.
How do I change a driver's round?: Change the customer's Area or Round Day in Delivery Customers. Rounds already made stay as they were; the next ones follow the new day.

Data Files is free

It comes with Rutba Office, with no account and no trial.

Download All the samples