Tansy Row Pharmacy

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 30 days

Batches still on the shelf that run out of date in the next 30 days, by supplier, with how many packs and their value at cost, and whether the supplier takes them back. Use it to put short-dated stock at the front, and to plan the next return.

From Batches, Products, Suppliers. Shows Supplier, Expiry Date, Days Left, Code, Product, Batch No, Quantity Left, Value at Cost, Takes Returns.

Calculated: Days Left is DateDiff("d", Date(), [Expiry Date]); Value at Cost is Round([Quantity Left] * [Cost Price], 2).

Only where Days Left Between 0 And 30 and Quantity Left > 0.

Prescriptions waiting over 7 days

Bags that have been ready for collection for more than a week, oldest first, with the shelf they are on. The counter rings or texts the customer about them.

From Prescriptions. Shows Ready On, Reference, Initials, Items, Days Waiting, Shelf.

Calculated: Days Waiting is DateDiff("d", [Ready On], Date()).

Only where Days Waiting > 7 and Status = Ready and For Delivery = No.

Deliveries today and tomorrow

Every stop on today's and tomorrow's rounds, by driver and stop, with the address, phone and bag reference.

From Deliveries, Delivery Rounds, Staff, Delivery Customers. Shows Delivery Date, Driver, Area, Stop, Customer, Address, Postcode, Phone, Prescription, Bags, Status.

Calculated: Driver is [Staff].[First Name] & " " & [Staff].[Last Name]; Customer is [Delivery Customers].[First Name] & " " & [Delivery Customers].[Last Name].

Only where Days Ahead Between 0 And 1.

Restricted sales by week

Pharmacist checks on pharmacy-only lines over the last 13 weeks, a line a week: how many checks, how many sold and not sold, and the packs sold. Week Of is the first day in the week a check was made.

From Pharmacist Checks, Products. Shows Week Of, Checks, Sold, Not Sold, Packs Sold, with totals.

Calculated: Week Of is Format([When], "yyyy-mm-dd"); Sold is IIf([Outcome] = "Sold", 1, 0); Not Sold is IIf([Outcome] = "Sold", 0, 1); Packs Sold is IIf([Outcome] = "Sold", [Pharmacist Checks].[Quantity], 0).

Only where Days Ago Between 0 And 90.

Pharmacist check log

Every pharmacist check in the last 30 days, newest first: the product, who it was for, which pharmacist, and the outcome.

From Pharmacist Checks, Products, Staff. Shows When, Product, Quantity, For Whom, Outcome, Note, Pharmacist.

Calculated: Pharmacist is [First Name] & " " & [Last Name].

Only where Days Ago Between 0 And 30.

Stock to reorder

Lines at or below their reorder level, by supplier, with how many to order. Discontinued lines are left out.

From Products, Suppliers. Shows Supplier, Code, Product, Pack, Units in Stock, Reorder Level, Reorder Quantity.

Only where Above Reorder Level <= 0 and Discontinued = No.

Takings by category

What each group of products took over the last 12 months, with the VAT within it and the takings before VAT.

From Sale Lines, Sales, Products, Categories. Shows Category, Items, Takings, VAT, Net, with totals.

Calculated: Takings is Round([Sale Lines].[Quantity] * [Unit Price], 2); VAT is Round(Round([Sale Lines].[Quantity] * [Unit Price], 2) - Round([Sale Lines].[Quantity] * [Unit Price], 2) / (1 + Nz([Sale Lines].[Tax Rate])), 2); Net is Round(Round([Sale Lines].[Quantity] * [Unit Price], 2) - Round(Round([Sale Lines].[Quantity] * [Unit Price], 2) - Round([Sale Lines].[Quantity] * [Unit Price], 2) / (1 + Nz([Sale Lines].[Tax Rate])), 2), 2).

Only where Days Ago Between 0 And 364.

Margin by category

Sales before VAT, their cost and the margin left for each group over the last 12 months, the largest margin first.

From Sale Lines, Sales, Products, Categories. Shows Category, Net Sales, Cost, Margin, with totals.

Calculated: Net Sales is Round(Round([Sale Lines].[Quantity] * [Unit Price], 2) - Round(Round([Sale Lines].[Quantity] * [Unit Price], 2) - Round([Sale Lines].[Quantity] * [Unit Price], 2) / (1 + Nz([Sale Lines].[Tax Rate])), 2), 2); Cost is Round([Sale Lines].[Quantity] * [Sale Lines].[Unit Cost], 2); Margin is Round(Round(Round([Sale Lines].[Quantity] * [Unit Price], 2) - Round(Round([Sale Lines].[Quantity] * [Unit Price], 2) - Round([Sale Lines].[Quantity] * [Unit Price], 2) / (1 + Nz([Sale Lines].[Tax Rate])), 2), 2) - Round([Sale Lines].[Quantity] * [Sale Lines].[Unit Cost], 2), 2).

Only where Days Ago Between 0 And 364.

Margin by product

Every line's shelf price, its price before VAT, its cost, and the margin on one pack in money and as a percentage. Use it when setting prices.

From Products, Categories. Shows Category, Code, Product, Retail Price, Price before VAT, Cost Price, Margin, Margin %.

Calculated: Price before VAT is Round([Retail Price] / (1 + [Tax Rate]), 2); Margin is Round([Retail Price] / (1 + [Tax Rate]) - [Cost Price], 2); Margin % is Round(100 * ([Retail Price] / (1 + [Tax Rate]) - [Cost Price]) / ([Retail Price] / (1 + [Tax Rate])), 1).

Only where Discontinued = No.

Takings by day

The till each day for the last fortnight: the number of sales, cash and card, the total, and the VAT within it.

From Sales. Shows Day, Sales, Cash, Card, Takings, VAT Included, with totals.

Calculated: Day is Format([When], "yyyy-mm-dd"); Cash is Round(IIf([Payment] = "Cash", [Total], 0), 2); Card is Round(IIf([Payment] = "Cash", 0, [Total]), 2).

Only where Days Ago Between 0 And 13.

Waste by month

Stock written off each month, by reason, in packs and at cost.

From Waste, Batches, Products. Shows Month, Reason, Units, Cost, with totals.

Calculated: Month is Format([Written Off On], "yyyy-mm"); Cost is Round([Quantity] * [Unit Cost], 2).

Supplier orders to come

Orders sent and not yet arrived, by the day they are expected.

From Supplier Orders, Suppliers. Shows Number, Supplier, Ordered On, Expected, Status.

Only where Status = Submitted.

Returns awaiting credit

Return notes not yet credited, with the credit claimed. The wholesalers take six weeks or more; chase any over ten weeks old.

From Supplier Returns, Suppliers. Shows Number, Supplier, Raised On, Collected On, Status, Credit Claimed.

Only where Status <> "Credited".

Prescriptions by month

How many prescriptions came in each month over the last year, with their items, how many went out for delivery, and how many were never collected.

From Prescriptions. Shows Month, Prescriptions, Items, Delivered, Not Collected, with totals.

Calculated: Month is Format([Received On], "yyyy-mm"); Delivered is IIf([For Delivery], 1, 0); Not Collected is IIf([Status] = "Not collected", 1, 0).

Only where Days Ago Between 0 And 365.

Stock value

Lines, packs on the shelf, and their value at cost and at shelf price, by group.

From Products, Categories. Shows Category, Lines, Units, Value at Cost, Value at Retail, with totals.

Calculated: Value at Cost is Round([Units in Stock] * [Cost Price], 2); Value at Retail is Round([Units in Stock] * [Retail Price], 2).

Forms

Every table has a form of the same name: Start Here, Business, Suppliers, Categories, Staff, Supplier Orders, Supplier Returns, Products, Supplier Order Lines, Batches, Waste, Sales, Sale Lines, Pharmacist Checks, Prescriptions, Delivery Customers, Delivery Rounds, Deliveries. 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.

Stock expiring in the next 30 days

The list for the weekly date check: every batch running out in the next 30 days, a page for each supplier, with the value at cost.

From the query Expiring in the next 30 days, a group for each Supplier: Code, Product, Batch No, Expiry Date, Days Left, Quantity Left, Value at Cost, with the total of Quantity Left, total of Value at Cost.

Prescriptions waiting over 7 days

Bags waiting over a week, with the shelf they are on, for the counter to ring about.

From the query Prescriptions waiting over 7 days: Reference, Initials, Items, Ready On, Days Waiting, Shelf, with the count of Reference, total of Items.

Deliveries today and tomorrow

Today's and tomorrow's stops for each driver, with the number of stops and bags.

From the query Deliveries today and tomorrow, a group for each Driver: Delivery Date, Stop, Customer, Address, Postcode, Phone, Prescription, Bags, with the count of Customer, total of Bags.

Stock to reorder

What is due on the next order, a page for each supplier.

From the query Stock to reorder, a group for each Supplier: Code, Product, Pack, Units in Stock, Reorder Level, Reorder Quantity, with the total of Reorder Quantity.

Takings by category, last 12 months

Takings, VAT and net sales by group for the last 12 months, with totals. For the owner and the accountant.

From the query Takings by category: Category, Items, Takings, VAT, Net, with the total of Items, total of Takings, total of VAT, total of Net.

Margin by category, last 12 months

Net sales, cost and margin by group for the last 12 months, with totals.

From the query Margin by category: Category, Net Sales, Cost, Margin, with the total of Net Sales, total of Cost, total of Margin.

Pharmacy medicines by week, last 13 weeks

Pharmacy-only lines asked for, sold and not sold, week by week, for the last 13 weeks.

From the query Restricted sales by week: Week Of, Checks, Sold, Not Sold, Packs Sold, with the total of Checks, total of Sold, total of Not Sold, total of Packs Sold.

Pharmacist checks, last 30 days

The last 30 days of pharmacist checks, for the pharmacist to look over and sign off.

From the query Pharmacist check log: When, Product, Quantity, For Whom, Outcome, Note, Pharmacist, with the count of Product.

Waste by month

Stock written off, month by month and by reason, at cost.

From the query Waste by month, a group for each Month: Reason, Units, Cost, with the total of Units, total of Cost.

Takings by day, last 14 days

The last fortnight's till, a line a day, with cash and card, for cashing up and banking.

From the query Takings by day: Day, Sales, Cash, Card, Takings, VAT Included, with the total of Sales, total of Takings, total of VAT Included.

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.