Fernleigh Estates

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.

Viewings in the next 7 days

Every viewing booked for the coming week, by day and time, with the applicant's phone and who is going.

From Viewings, Properties, Applicants, Staff. Shows Viewing Date, Viewing Time, Reference, Property, Purpose, Applicant, Phone, Accompanied By, Confirmation Sent.

Calculated: Applicant is [Applicants].[First Name] & " " & [Applicants].[Last Name]; Accompanied By is [Staff].[First Name] & " " & [Staff].[Last Name].

Only where Days Until Between 0 And 7 and Status = Booked.

Offers awaiting a decision

Offers the owner has not yet answered, oldest first, with the asking price, the offer as a percentage of it, how the buyer is buying, and the seller's phone.

From Offers, Properties, Applicants, Clients. Shows Made On, Property, Asking Price, Offer, % of Asking, Buyer, Position, Conditions, Seller, Seller Phone, Days Waiting.

Calculated: % of Asking is Round([Amount] * 100 / [Properties].[Price], 1); Buyer is [Applicants].[First Name] & " " & [Applicants].[Last Name]; Seller is [Clients].[First Name] & " " & [Clients].[Last Name]; Days Waiting is DateDiff("d", [Made On], Date()).

Only where Status = Awaiting decision.

Sales in progress

Every agreed sale not yet completed, in the order of its stage, with how long since the offer was accepted, the completion hoped for and the fee to come.

From Sales, Properties, Applicants, Clients, Staff. Shows Stage, Sale, Property, Price, Accepted On, Days Since Offer, Target Completion, Buyer, Seller, Negotiator, Fee Due.

Calculated: Days Since Offer is DateDiff("d", [Accepted On], Date()); Buyer is [Applicants].[First Name] & " " & [Applicants].[Last Name]; Seller is [Clients].[First Name] & " " & [Clients].[Last Name]; Negotiator is [Staff].[First Name] & " " & [Staff].[Last Name]; Fee Due is Round([Sales].[Price] * [Sales].[Fee Rate], 2).

Only where Stage Not In ("Completed", "Fell through").

Sales by stage

How many sales are at each stage, their value and the fees they will bring.

From Sales, Properties. Shows Stage, Sales, Value, Fees to Come, with totals.

Calculated: Fees to Come is Round([Sales].[Price] * [Sales].[Fee Rate], 2).

Only where Stage Not In ("Completed", "Fell through").

On the market over 60 days

Homes for sale or to let that have been on the market more than 60 days, the longest first, to talk to the owner about the price.

From Properties, Areas, Clients. Shows Reference, Address, Area, Purpose, Price, Listed On, Days on Market, Client, Phone.

Calculated: Days on Market is DateDiff("d", [Listed On], Date()); Client is [Clients].[First Name] & " " & [Clients].[Last Name].

Only where Days on Market > 60 and Status In ("For sale", "To let").

Rent collected and owed to landlords

For each landlord, the rent due in the last 30 days, what was collected and what is outstanding, the fees and VAT on it, and what is owed to the landlord before any repairs.

From Rent Charges, Tenancies, Properties, Clients. Shows Landlord, Account, Rents Due, Rent Due, Collected, Outstanding, Fees and VAT, Owed to Landlord, with totals.

Calculated: Landlord is [Clients].[First Name] & " " & [Clients].[Last Name]; Collected is Nz([Received]); Outstanding is Round([Amount] - Nz([Received]), 2); Fees and VAT is Round(Nz([Received]) * [Management Fee], 2) + Round(Round(Nz([Received]) * [Management Fee], 2) * 0.2, 2); Owed to Landlord is Round(Nz([Received]) - (Round(Nz([Received]) * [Management Fee], 2) + Round(Round(Nz([Received]) * [Management Fee], 2) * 0.2, 2)), 2).

Only where Days Ago Between 0 And 30.

Commission earned by month

The agency's fees before VAT each month: sales commission, letting fees and management fees.

From Invoice Lines, Agency Invoices. Shows Month, Sales, Letting Fees, Management Fees, Total, with totals.

Calculated: Month is Format([Invoice Date], "yyyy-mm"); Sales is IIf([Kind] = "Sale commission", [Invoice Lines].[Net], 0); Letting Fees is IIf([Kind] = "Letting fee", [Invoice Lines].[Net], 0); Management Fees is IIf([Kind] = "Management fee", [Invoice Lines].[Net], 0).

Applicants matching a property

For each home on the market, the active applicants in its area who want to buy or rent (whichever it is for), can afford it and want no more bedrooms than it has. Call them first.

From Properties, Areas, Applicants. Shows Reference, Property, Area, Purpose, Bedrooms, Price, Applicant, Phone, Budget, Min Bedrooms, Position.

Calculated: Applicant is [Applicants].[First Name] & " " & [Applicants].[Last Name].

Only where Status In ("For sale", "To let") and Status = Active and Same Purpose = Yes and Within Budget = Yes and Enough Bedrooms = Yes.

Rent arrears

Rent on managed tenancies more than three days late and not fully paid, by tenant, with the landlord.

From Rent Charges, Tenancies, Properties, Applicants, Clients. Shows Tenant, Property, Landlord, Period, Due Date, Amount, Received, Owed, Days Late.

Calculated: Tenant is [Applicants].[First Name] & " " & [Applicants].[Last Name]; Landlord is [Clients].[First Name] & " " & [Clients].[Last Name]; Owed is Round([Amount] - Nz([Received]), 2); Days Late is DateDiff("d", [Due Date], Date()).

Only where Owed > 0 and Days Late > 3.

Tenancies ending in the next 60 days

Tenancies whose fixed term ends within two months, to ask the landlord and tenant about renewing.

From Tenancies, Properties, Applicants, Clients. Shows End, Days Left, Property, Tenant, Landlord, Monthly Rent, Service.

Calculated: Days Left is DateDiff("d", Date(), [End]); Tenant is [Applicants].[First Name] & " " & [Applicants].[Last Name]; Landlord is [Clients].[First Name] & " " & [Clients].[Last Name].

Only where Days Left Between 0 And 60 and Status = Active.

Open repairs

Repairs at managed homes not yet done or cancelled, oldest first, with the quote and the day the contractor is booked for.

From Repairs, Properties. Shows Reported, Property, Problem, Priority, Status, Quote, Booked For, Days Open.

Calculated: Days Open is DateDiff("d", [Reported], Date()).

Only where Status Not In ("Done", "Cancelled").

Paid to landlords by month

Rent collected, deducted and paid to landlords each month over the last year, from the statements.

From Landlord Statements. Shows Month, Statements, Rent Collected, Deducted, Paid to Landlords, with totals.

Only where Days Ago Between 0 And 366.

Asking prices by area

For each area, the homes on the market for sale and to let, with the lowest, average and highest price asked.

From Properties, Areas. Shows Area, Purpose, On the Market, Lowest, Average, Highest, with totals.

Calculated: Average is Round([Price], 0).

Only where Status In ("For sale", "To let", "Under offer", "Let agreed").

Forms

Every table has a form of the same name: Start Here, Business, Staff, Areas, Clients, Properties, Applicants, Viewings, Offers, Solicitors, Sales, Tenancies, Landlord Statements, Statement Lines, Rent Charges, Contractors, Repairs, Agency Invoices, Invoice Lines. 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.

Viewings in the next 7 days

The week's viewings a page a day, with the time, the home, the applicant's phone and who is going: for the office wall or the negotiator's car.

From the query Viewings in the next 7 days, a group for each Viewing Date: Viewing Time, Property, Purpose, Applicant, Phone, Accompanied By.

Offers awaiting a decision

The offers waiting for an owner's answer, to go through at the morning meeting.

From the query Offers awaiting a decision: Made On, Property, Asking Price, Offer, % of Asking, Buyer, Position, Seller.

Sales in progress

Every sale in progress grouped by stage, with the value and the fees to come: the sales progressor's weekly list.

From the query Sales in progress, a group for each Stage: Sale, Property, Price, Accepted On, Days Since Offer, Target Completion, Buyer, Negotiator, Fee Due, with the total of Price, total of Fee Due.

On the market over 60 days

Homes on the market more than 60 days, with the owner's phone, for the price review.

From the query On the market over 60 days: Reference, Address, Area, Purpose, Price, Listed On, Days on Market, Client, Phone.

Rent collected and owed to landlords, last 30 days

Each landlord's rent for the last 30 days, collected and outstanding, with the fees and what they are owed, and the totals.

From the query Rent collected and owed to landlords: Landlord, Account, Rent Due, Collected, Outstanding, Fees and VAT, Owed to Landlord, with the total of Rent Due, total of Collected, total of Outstanding, total of Fees and VAT, total of Owed to Landlord.

Commission by month

One line a month with the fees from sales, letting and management, and the totals.

From the query Commission earned by month: Month, Sales, Letting Fees, Management Fees, Total, with the total of Sales, total of Letting Fees, total of Management Fees, total of Total.

Rent arrears

Each tenant behind with rent on their own section, every unpaid month and the total owed.

From the query Rent arrears, a group for each Tenant: Property, Landlord, Period, Due Date, Days Late, Amount, Received, Owed, with the total of Owed.

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.