Ironbridge Repairs

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.

Job board

Every job not yet collected, in the order the shop works through them, with who it belongs to, the technician, the promised date and how many days are left (below nought means it is late). Open it first thing in the morning.

From Jobs, Devices, Customers, Staff. Shows Stage, Job, Booked In, Customer, Phone, Device, Fault, Technician, Promised, Days Left.

Calculated: Stage is IIf([Status] = "Booked in", "1 Booked in", IIf([Status] = "Diagnosing", "2 Diagnosing", IIf([Status] = "Awaiting approval", "3 Awaiting approval", IIf([Status] = "Awaiting parts", "4 Awaiting parts", IIf([Status] = "Repairing", "5 Repairing", IIf([Status] = "Ready", "6 Ready", "7 " & [Status])))))); Customer is [Customers].[First Name] & " " & [Customers].[Last Name]; Device is [Make] & " " & [Model]; Technician is [Staff].[First Name] & " " & [Staff].[Last Name]; Days Left is IIf(Nz([Finished], "") = "", DateDiff("d", Date(), [Promised]), Null).

Only where Collected Is Null.

Jobs by status

How many jobs sit at each stage now, and the oldest booking at each. A quick count for the morning meeting.

From Jobs. Shows Stage, Jobs, Oldest Booked In, with totals.

Calculated: Stage is IIf([Status] = "Booked in", "1 Booked in", IIf([Status] = "Diagnosing", "2 Diagnosing", IIf([Status] = "Awaiting approval", "3 Awaiting approval", IIf([Status] = "Awaiting parts", "4 Awaiting parts", IIf([Status] = "Repairing", "5 Repairing", IIf([Status] = "Ready", "6 Ready", "7 " & [Status])))))).

Only where Collected Is Null.

Awaiting parts

Jobs held up for a part, with the part, the supplier and when it is expected. Use it when a customer rings to ask, or to chase a supplier.

From Part Orders, Parts, Suppliers, Jobs, Devices, Customers. Shows Job, Customer, Phone, Device, Part, Supplier, Ordered On, Expected, Promised.

Calculated: Customer is [Customers].[First Name] & " " & [Customers].[Last Name]; Device is [Make] & " " & [Model].

Only where Received On Is Null and Status = Awaiting parts.

Past promised date

Unfinished jobs whose promised date has gone by, latest first, with the customer's number so the counter can ring them with news.

From Jobs, Devices, Customers, Staff. Shows Job, Status, Customer, Phone, Device, Technician, Promised, Days Late.

Calculated: Customer is [Customers].[First Name] & " " & [Customers].[Last Name]; Device is [Make] & " " & [Model]; Technician is [Staff].[First Name] & " " & [Staff].[Last Name]; Days Late is DateDiff("d", [Promised], Date()).

Only where Days Late > 0 and Finished Is Null.

Ready for collection

Every finished device still on the shelf, longest waiting first, with what is left to pay and how the customer wants to be told. The calling list.

From Jobs, Devices, Customers. Shows Job, Customer, Phone, Contact By, Device, Outcome, Finished, Days Waiting, To Pay.

Calculated: Customer is [Customers].[First Name] & " " & [Customers].[Last Name]; Device is [Make] & " " & [Model]; Days Waiting is DateDiff("d", [Finished], Date()); To Pay is Round(Nz([Charge]) - Nz([Deposit]), 2).

Only where Finished Is Not Null and Collected Is Null.

Uncollected 14 days or more

Devices ready for two weeks or longer and not picked up, with any reminders already sent. Use it to send a final reminder before the shop's storage terms apply.

From Jobs, Devices, Customers. Shows Job, Customer, Phone, E-mail, Device, Finished, Days Waiting, To Pay, Notes.

Calculated: Customer is [Customers].[First Name] & " " & [Customers].[Last Name]; Device is [Make] & " " & [Model]; Days Waiting is DateDiff("d", [Finished], Date()); To Pay is Round(Nz([Charge]) - Nz([Deposit]), 2).

Only where Days Waiting >= 14 and Collected Is Null.

Quotes awaiting approval

Quotes given and not yet answered, oldest first, with the price and the phone number to follow up.

From Jobs, Devices, Customers. Shows Job, Quoted On, Days Waiting, Customer, Phone, Device, Fault, Quote.

Calculated: Days Waiting is DateDiff("d", [Quoted On], Date()); Customer is [Customers].[First Name] & " " & [Customers].[Last Name]; Device is [Make] & " " & [Model].

Only where Status = Awaiting approval.

Parts to reorder

Shelf parts at or below their reorder level, by supplier, with what a full reorder would cost.

From Parts, Suppliers. Shows Supplier, Code, Part, In Stock, Reorder Level, Reorder Quantity, Cost, Order Value.

Calculated: Order Value is Round([Reorder Quantity] * [Cost], 2).

Only where Spare <= 0 and Stocked = Yes.

Parts on order

Everything ordered and not yet arrived, with the expected date and the record number of the job it is for when there is one.

From Part Orders, Parts, Suppliers. Shows Supplier, Part, Quantity, Unit Cost, Ordered On, Expected, For Job.

Only where Received On Is Null.

Takings by month

Money taken each month, deposits and account payments included, and how many payments made it up.

From Payments. Shows Month, Payments, Takings, with totals.

Calculated: Month is Format([Paid On], "yyyy-mm").

Takings last 30 days

The last thirty days of takings split by payment method, for checking against the card machine and the bank statement.

From Payments. Shows Method, Payments, Takings, with totals.

Only where Days Ago Between 0 And 30.

Technician workload

How many unfinished jobs each technician has, how many are stuck on parts or quotes, and their oldest job.

From Jobs, Staff. Shows Technician, Open Jobs, Awaiting Parts, Awaiting Approval, Oldest Booked In, with totals.

Calculated: Technician is [Staff].[First Name] & " " & [Staff].[Last Name]; Awaiting Parts is IIf([Status] = "Awaiting parts", 1, 0); Awaiting Approval is IIf([Status] = "Awaiting approval", 1, 0).

Only where Finished Is Null.

Technician turnaround

For repairs finished in the last year: how many each technician did, the average days from booking in to finished, and what they were charged at.

From Jobs, Staff. Shows Technician, Jobs Repaired, Average Days, Charged, with totals.

Calculated: Technician is [Staff].[First Name] & " " & [Staff].[Last Name]; Average Days is DateDiff("d", [Booked In], [Finished]).

Only where Outcome = Repaired and Days Ago Between 0 And 365.

Work by device kind

Jobs and money by kind of device over the whole period, with the average days each kind takes.

From Jobs, Devices. Shows Kind, Jobs, Charged, Average Days, with totals.

Calculated: Average Days is DateDiff("d", [Booked In], [Finished]).

Warranty returns

Jobs that came back under warranty, newest first; the fault names the first job, so repeat faults on a part or a technician's work show up.

From Jobs, Devices, Customers, Staff. Shows Job, Booked In, Customer, Device, Fault, Technician, Status.

Calculated: Customer is [Customers].[First Name] & " " & [Customers].[Last Name]; Device is [Make] & " " & [Model]; Technician is [Staff].[First Name] & " " & [Staff].[Last Name].

Only where Warranty Of Is Not Null.

Job sheets

Every job booked in over the last fourteen days with its full detail: serial, fault, what was left with it, quote and promised date.

From Jobs, Devices, Customers, Staff. Shows Technician, Job, Booked In, Customer, Phone, Device, Serial, Fault, Left With It, Status, Quote, Promised.

Calculated: Technician is [Staff].[First Name] & " " & [Staff].[Last Name]; Customer is [Customers].[First Name] & " " & [Customers].[Last Name]; Device is [Make] & " " & [Model].

Only where Days Ago Between 0 And 14.

Parts margin

Each part fitted over the period: how many, what they were charged at with VAT, and the margin over cost on the price without VAT. Warranty fittings count against the margin.

From Job Lines, Parts. Shows Fits, Part, Fitted, Charged, Margin, with totals.

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

Only where Done On Is Not Null.

Accounts to chase

Every invoice a business account has not paid yet, by account and due date, with how many days overdue it is (nought when it is not due yet).

From Invoices, Accounts, Jobs. Shows Account, Contact, E-mail, Invoice, Job, Invoice Date, Due Date, Balance, Days Overdue.

Calculated: Days Overdue is IIf(DateDiff("d", [Due Date], Date()) > 0, DateDiff("d", [Due Date], Date()), 0).

Only where Paid On Is Null.

Under warranty

Repairs still inside their 90 days, with the certificate number and what it covers, those ending soonest first. Look a customer up here when a device comes back.

From Warranties, Jobs, Devices, Customers. Shows Certificate, Job, Customer, Phone, Device, Serial, Covers, Warranty Until, Days Left.

Calculated: Customer is [Customers].[First Name] & " " & [Customers].[Last Name]; Device is [Make] & " " & [Model]; Days Left is DateDiff("d", Date(), [Warranty Until]).

Only where Days Left >= 0.

VAT by month

For each month, the invoices raised, their net value, the VAT inside them and the total: the figures for the VAT return.

From Invoices. Shows Month, Invoices, Net Sales, VAT, Invoiced, with totals.

Calculated: Month is Format([Invoice Date], "yyyy-mm").

Forms

Every table has a form of the same name: Start Here, Business, Accounts, Customers, Staff, Suppliers, Parts, Devices, Jobs, Part Orders, Job Lines, Quotes, Quote Lines, Invoices, Warranties, Payments. 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.

Job board

The job board printed for the bench, one section per stage, each job with its customer, device, fault, technician and promised date.

From the query Job board, a group for each Stage: Job, Booked In, Customer, Device, Fault, Technician, Promised, Days Left, with the count of Job.

Ready for collection: to phone

A calling list for the counter: each device waiting to be collected, the customer's number, how they like to be contacted and what they owe, longest waiting first.

From the query Ready for collection: Job, Customer, Phone, Contact By, Device, Outcome, Finished, Days Waiting, To Pay, with the count of Job, total of To Pay.

Parts to order

The parts to reorder, one section per supplier, with the cost of each order. Take it to the phone or the supplier's website.

From the query Parts to reorder, a group for each Supplier: Code, Part, In Stock, Reorder Level, Reorder Quantity, Cost, Order Value, with the total of Order Value.

Takings by month

Monthly takings with a grand total, for the accountant or the owner's month-end look.

From the query Takings by month: Month, Payments, Takings, with the total of Payments, total of Takings.

Job sheets: the last two weeks

The last two weeks of jobs, one section per technician, with every detail taken at the counter. Print it to check work in progress against the shelf.

From the query Job sheets, a group for each Technician: Job, Booked In, Customer, Phone, Device, Serial, Fault, Left With It, Status, Quote, Promised, with the count of Job.

Technician turnaround

Each technician's finished repairs over the last year, their average turnaround and the value of their work.

From the query Technician turnaround: Technician, Jobs Repaired, Average Days, Charged, with the total of Jobs Repaired, total of Charged.

Account invoices due

Unpaid account invoices, a section for each business, with days overdue and the total owed: the list for chasing payment.

From the query Accounts to chase, a group for each Account: Invoice, Job, Invoice Date, Due Date, Days Overdue, Balance, with the total of Balance.

VAT by month, from the invoices

Net sales, VAT and invoiced totals for each month with the grand totals, to fill in the VAT return or hand to the accountant.

From the query VAT by month: Month, Invoices, Net Sales, VAT, Invoiced, with the total of Net Sales, total of VAT, total of Invoiced.

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.