Copperbeech Motors

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.

Stock by days in stock

Every car not yet sold, the longest in stock first, with its asking price and status. The forecourt list.

From Vehicles. Shows Stock No, Registration, Vehicle, Mileage, Fuel, Asking Price, Status, Days in Stock.

Calculated: Vehicle is [Year] & " " & [Make] & " " & [Model]; Days in Stock is DateDiff("d", [Bought On], Date()).

Only where Status Not In ("Sold").

Stock over 60 days

Cars on sale or in preparation for more than sixty days, with what each has cost so far and the margin left at its asking price: the cars to reduce, advertise harder or send back to auction.

From Vehicles. Shows Stock No, Registration, Vehicle, Bought On, Days in Stock, Cost to Date, First Asking Price, Asking Price, Margin at Asking, Status.

Calculated: Vehicle is [Year] & " " & [Make] & " " & [Model]; Days in Stock is DateDiff("d", [Bought On], Date()); Cost to Date is Round([Purchase Price] + Nz([Buying Fee]) + Nz([Prep Cost]), 2); Margin at Asking is Round([Asking Price] - [Purchase Price] - Nz([Buying Fee]) - Nz([Prep Cost]), 2).

Only where Days in Stock > 60 and Status In ("For sale", "In preparation").

Margin per vehicle

Each car sold, the newest first: what it sold for, what it cost, the buying fee and preparation, the VAT on the margin and what is left, and how long it took to sell.

From Sales, Vehicles, Staff. Shows Handover, Stock No, Registration, Vehicle, Salesperson, Sold For, Bought For, Buying Fee, Prep Cost, VAT on Margin, Margin, Days in Stock.

Calculated: Vehicle is [Year] & " " & [Make] & " " & [Model]; Salesperson is [Staff].[First Name] & " " & [Staff].[Last Name]; VAT on Margin is IIf([Sales].[Price] > [Purchase Price], Round(([Sales].[Price] - [Purchase Price]) / 6, 2), 0); Margin is Round([Sales].[Price] - [Purchase Price] - Nz([Buying Fee]) - Nz([Prep Cost]) - IIf([Sales].[Price] > [Purchase Price], Round(([Sales].[Price] - [Purchase Price]) / 6, 2), 0), 2); Days in Stock is DateDiff("d", [Bought On], [Handover]).

Only where Status = Completed.

Sales by month

Cars handed over each month, with the takings and the gross margin on the cars.

From Sales, Vehicles. Shows Month, Cars Sold, Vehicle Sales, Takings, Gross Margin, with totals.

Calculated: Month is Format([Handover], "yyyy-mm"); Gross Margin is Round([Sales].[Price] - [Purchase Price] - Nz([Buying Fee]) - Nz([Prep Cost]) - IIf([Sales].[Price] > [Purchase Price], Round(([Sales].[Price] - [Purchase Price]) / 6, 2), 0), 2).

Only where Status = Completed.

Sales by salesperson

Cars sold, takings and gross margin for each salesperson over the last twelve months, the best first.

From Sales, Staff, Vehicles. Shows Salesperson, Cars Sold, Takings, Gross Margin, with totals.

Calculated: Salesperson is [Staff].[First Name] & " " & [Staff].[Last Name]; Gross Margin is Round([Sales].[Price] - [Purchase Price] - Nz([Buying Fee]) - Nz([Prep Cost]) - IIf([Sales].[Price] > [Purchase Price], Round(([Sales].[Price] - [Purchase Price]) / 6, 2), 0), 2).

Only where Status = Completed and Days Ago Between 0 And 365.

Deposits on unsold vehicles

Reserved cars: the deposit held, the finance and the balance still to come, by handover date. Use it each morning to get the cars ready.

From Sales, Vehicles, Customers, Staff. Shows Order No, Agreed On, Customer, Phone, Registration, Vehicle, Deposit, Finance Amount, Balance at Handover, Handover, Salesperson.

Calculated: Customer is [Customers].[First Name] & " " & [Customers].[Last Name]; Vehicle is [Year] & " " & [Make] & " " & [Model]; Salesperson is [Staff].[First Name] & " " & [Staff].[Last Name].

Only where Status = Reserved.

Test drives that did not buy

Test drives in the last thirty days that did not end in a sale, the newest first, with the customer's phone and what they said: the follow-up calls.

From Test Drives, Customers, Vehicles, Staff. Shows When, Customer, Phone, E-mail, Registration, Vehicle, Salesperson, Outcome, Notes, Days Ago.

Calculated: Customer is [Customers].[First Name] & " " & [Customers].[Last Name]; Vehicle is [Year] & " " & [Make] & " " & [Model]; Salesperson is [Staff].[First Name] & " " & [Staff].[Last Name]; Days Ago is DateDiff("d", [When], Date()).

Only where Outcome In ("Thinking it over", "Not for them", "Bought elsewhere") and Days Ago Between 0 And 30.

Test drives booked

Test drives booked for today and the next seven days, in time order.

From Test Drives, Customers, Vehicles, Staff. Shows When, Customer, Phone, Registration, Vehicle, Vehicle Status, With.

Calculated: Customer is [Customers].[First Name] & " " & [Customers].[Last Name]; Vehicle is [Year] & " " & [Make] & " " & [Model]; With is [Staff].[First Name] & " " & [Staff].[Last Name].

Only where Outcome = Booked and Days Ahead Between 0 And 7.

Warranties expiring

Warranties that run out in the next sixty days, the soonest first, with the customer's phone: who to offer a renewal or a service.

From Warranties, Warranty Plans, Sales, Customers, Vehicles. Shows Ends, Days Left, Certificate No, Plan, Customer, Phone, Registration, Vehicle, Mileage at Start.

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

Only where Days Left Between 0 And 60.

Finance commission by month

Finance agreements paid out each month by lender, with the amount financed and the commission earned.

From Finance Agreements, Lenders, Sales. Shows Month, Lender, Agreements, Amount Financed, Commission, with totals.

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

Only where Status = Paid out.

Warranty sales by plan

Warranties sold in the last twelve months by plan, with what they brought in, what they cost and the profit.

From Warranties, Warranty Plans. Shows Plan, Sold, Takings, Cost, Profit, with totals.

Calculated: Profit is Round([Warranties].[Price] - [Warranties].[Cost], 2).

Only where Days Ago Between 0 And 365.

Recent part-exchange valuations

Valuations in the last thirty days, the newest first, with what was offered and how each ended.

From Part Exchanges, Customers, Staff. Shows Number, Valued On, Customer, Registration, Vehicle, Mileage, Guide Value, Offered, Valid Until, Outcome, Valued By.

Calculated: Customer is [Customers].[First Name] & " " & [Customers].[Last Name]; Vehicle is [Part Exchanges].[Year] & " " & [Part Exchanges].[Make] & " " & [Part Exchanges].[Model]; Valued By is [Staff].[First Name] & " " & [Staff].[Last Name].

Only where Days Ago Between 0 And 30.

Purchases by source

Cars bought in the last twelve months from auctions, the trade and part-exchange: how many, what they cost, the fees and the preparation.

From Vehicles. Shows Source, Bought, Spent, Fees, Preparation, with totals.

Only where Days Ago Between 0 And 365.

Preparation by vehicle

For each car still in stock, the preparation jobs and their cost beside the Prep Cost on its record, to check the two agree.

From Preparation, Vehicles. Shows Stock No, Registration, Vehicle, Status, Jobs, Cost, Prep Cost on Vehicle, with totals.

Calculated: Vehicle is [Year] & " " & [Make] & " " & [Model].

Only where Status Not In ("Sold").

Preparation spend by kind

What was spent on valets, services, MOTs, repairs, tyres and bodywork in the last twelve months.

From Preparation. Shows Kind, Jobs, Cost, with totals.

Only where Days Ago Between 0 And 365.

Forms

Every table has a form of the same name: Start Here, Business, Staff, Suppliers, Customers, Vehicles, Vehicle Features, Preparation, Test Drives, Sales, Sale Lines, Lenders, Finance Agreements, Warranty Plans, Warranties, Part Exchanges. 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 list

The forecourt list by status, with mileage, asking price and days in stock, and the stock's value at asking prices.

From the query Stock by days in stock, a group for each Status: Stock No, Registration, Vehicle, Mileage, Fuel, Asking Price, Days in Stock, with the total of Asking Price, count of Stock No.

Aged stock

The cars over sixty days, with what they have cost and the margin left, for the weekly stock meeting.

From the query Stock over 60 days: Stock No, Registration, Vehicle, Days in Stock, Cost to Date, Asking Price, Margin at Asking, with the total of Cost to Date, total of Margin at Asking.

Vehicle margins

Every car sold with its costs and margin, and the total and average margin.

From the query Margin per vehicle: Handover, Registration, Vehicle, Salesperson, Sold For, Bought For, Prep Cost, VAT on Margin, Margin, with the total of Margin, average of Margin.

Monthly sales

One line a month: cars sold, takings and gross margin, with the totals.

From the query Sales by month: Month, Cars Sold, Vehicle Sales, Takings, Gross Margin, with the total of Cars Sold, total of Takings, total of Gross Margin.

Salesperson league

The sales team over the last twelve months, by gross margin.

From the query Sales by salesperson: Salesperson, Cars Sold, Takings, Gross Margin, with the total of Cars Sold, total of Gross Margin.

Deposits held

Every deposit held on a reserved car, with the handover date and the total held.

From the query Deposits on unsold vehicles: Order No, Customer, Registration, Vehicle, Handover, Deposit, Balance at Handover, with the total of Deposit.

Test drive follow-ups

The calls to make, on a page for each salesperson.

From the query Test drives that did not buy, a group for each Salesperson: When, Customer, Phone, Vehicle, Outcome, with the count of Customer.

Warranty renewals

Warranties running out in the next sixty days, for the renewal letters and calls.

From the query Warranties expiring: Ends, Certificate No, Plan, Customer, Phone, Registration, Vehicle, with the count of Certificate No.

Finance commission

Commission earned from each lender, month by month.

From the query Finance commission by month, a group for each Month: Lender, Agreements, Amount Financed, Commission, with the total of Commission, total of Amount Financed.

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.