Bramble Studio

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.

Unbilled time

Billable hours on hourly projects not yet on an invoice, by client, project and person, with what they are worth at charge-out rates. Run it before the monthly invoices go out.

From Time Entries, Projects, Clients, Staff. Shows Client, Project, Person, Earliest, Hours, Value, with totals.

Calculated: Person is [First Name] & " " & [Last Name]; Value is Round([Hours] * [Charge-out Rate], 2).

Only where Kind = Hourly and Billable = Yes and Invoice Is Null.

Unbilled expenses

Rechargeable costs not yet passed on to the client, oldest first, so nothing is forgotten on the next invoice.

From Expenses, Projects, Clients. Shows Client, Project, Date, Category, Description, Amount.

Only where Rechargeable = Yes and Invoice Is Null.

Project budgets

Each project in hand: its budget hours against the hours used so far, and its fee against the time used at charge-out rates and at cost. A project whose time at rates is near its fee needs a word with the client.

From Time Entries, Projects, Clients, Staff. Shows Client, Code, Project, Kind, Due Date, Budget Hours, Hours Used, Fee, Time at Rates, Cost of Time, with totals.

Calculated: Time at Rates is Round([Hours] * [Charge-out Rate], 2); Cost of Time is Round([Hours] * [Hourly Cost], 2).

Only where Kind <> "Internal" and Status In ("Active", "On hold").

Task hours against estimate

For each project in hand, each task's estimated hours against the hours logged to it, to see which part of the work is running over.

From Time Entries, Tasks, Projects. Shows Code, Project, Task, Role, Estimated Hours, Hours Used, with totals.

Only where Status In ("Active", "On hold") and Kind <> "Internal".

Hours by person, last 30 days

Each person's hours over the last 30 days, how many of them were billable and what those were worth. Use it to see who is stretched and who has room.

From Time Entries, Staff. Shows Person, Role, Hours, Billable Hours, Billable Value, with totals.

Calculated: Person is [First Name] & " " & [Last Name]; Billable Hours is IIf([Billable], [Hours], 0); Billable Value is Round(IIf([Billable], [Hours] * [Charge-out Rate], 0), 2).

Only where Days Ago Between 0 And 30.

Time sheet

Every entry of the last 14 days, by person and day, with the client, project and task. For checking time sheets before invoicing.

From Time Entries, Tasks, Projects, Clients, Staff. Shows Person, Date, Client, Project, Task, Hours, Billable, Note.

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

Only where Days Ago Between 0 And 13.

Overdue invoices

Invoices past their due date with money still owed, the longest overdue first, with the client's phone number for chasing.

From Invoices, Projects, Clients. Shows Invoice, Client, Project, Invoice Date, Due Date, Total, Paid, Outstanding, Days Overdue, Client Phone.

Calculated: Outstanding is Round([Total] - [Paid], 2); Days Overdue is DateDiff("d", [Invoices].[Due Date], Date()).

Only where Outstanding > 0 and Days Overdue > 0.

Unpaid invoices

Every invoice with money still owed, by client, with the days it is overdue (below nought when it is not due yet).

From Invoices, Projects, Clients. Shows Client, Invoice, Project, Invoice Date, Due Date, Total, Outstanding, Days Overdue.

Calculated: Outstanding is Round([Total] - [Paid], 2); Days Overdue is DateDiff("d", [Invoices].[Due Date], Date()).

Only where Outstanding > 0.

Revenue by client

Each client's invoiced fees before VAT, last year and this year, the biggest this year first. Shows who the studio depends on.

From Invoices, Projects, Clients. Shows Client, Sector, Last Year, This Year, Invoices, with totals.

Calculated: Last Year is Round(IIf(Year([Invoice Date]) = Year(Date()) - 1, [Net], 0), 2); This Year is Round(IIf(Year([Invoice Date]) = Year(Date()), [Net], 0), 2).

Revenue by month

What was invoiced each month, net, VAT and total, for seeing the busy and quiet months.

From Invoices. Shows Month, Invoices, Net, VAT, Total, with totals.

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

Income by kind

Invoiced amounts each year split into fixed-fee stages, hourly time and recharged expenses.

From Invoice Lines, Invoices. Shows Year, Kind, Lines, Amount, with totals.

Calculated: Year is Format([Invoice Date], "yyyy"); Amount is Round([Quantity] * [Unit Price], 2).

Cash received by month

The money that actually came in each month, which is what pays the wages.

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

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

Project profit

Completed projects, newest first: the fee earned against the cost of the time spent, and the time at charge-out rates. Fee less cost of time is what the project made towards the studio's other costs.

From Time Entries, Projects, Clients, Staff. Shows Finished On, Code, Project, Client, Kind, Fee, Hours, Time at Rates, Cost of Time, with totals.

Calculated: Time at Rates is Round([Hours] * [Charge-out Rate], 2); Cost of Time is Round([Hours] * [Hourly Cost], 2).

Only where Kind <> "Internal" and Status = Completed.

Projects due

Projects in hand due within the next 30 days, and any already late (Days Left below nought), soonest first.

From Projects, Clients. Shows Due Date, Days Left, Code, Project, Client, Kind, Start Date, Status.

Calculated: Days Left is DateDiff("d", Date(), [Due Date]).

Only where Days Left <= 30 and Status In ("Active", "On hold").

Proposals waiting

Proposals sent and not yet answered, the oldest first, with how long each has waited. For a follow-up call.

From Projects, Clients. Shows Proposed On, Days Waiting, Code, Project, Client, Kind, Budget Hours, Fee, Could Start.

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

Only where Status = Proposal.

Recent invoice lines

The lines of every invoice sent in the last 45 days, for checking what went out.

From Invoice Lines, Invoices, Projects, Clients. Shows Invoice, Invoice Date, Client, Project, Description, Quantity, Unit Price, Amount.

Calculated: Amount is Round([Quantity] * [Unit Price], 2).

Only where Days Ago Between 0 And 45.

Client contacts

The people at each client, the main contact first, with their e-mail and phone.

From Contacts, Clients. Shows Client, Main Contact, First Name, Last Name, Job Title, E-mail, Phone.

Forms

Every table has a form of the same name: Start Here, Business, Clients, Contacts, Staff, Projects, Tasks, Time Sheets, Time Entries, Expenses, Invoices, Invoice Lines, 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.

Time sheet, last 14 days

The last 14 days of time, one section per person with their total hours. For each person to check, and for the project manager before invoicing.

From the query Time sheet, a group for each Person: Date, Client, Project, Task, Hours, Note, with the total of Hours.

Work in progress to invoice

Unbilled hourly time by client, with hours and value, and a total for the studio: what the next round of invoices will bring in.

From the query Unbilled time, a group for each Client: Project, Person, Earliest, Hours, Value, with the total of Hours, total of Value.

Invoices due

Money owed by each client, invoice by invoice, with the days overdue and the total outstanding. For the partner who chases payment.

From the query Unpaid invoices, a group for each Client: Invoice, Project, Invoice Date, Due Date, Days Overdue, Total, Outstanding, with the total of Outstanding.

Project budget report

Projects in hand by client, with budget hours, hours used, fee, time at rates and cost of time. For the weekly project meeting.

From the query Project budgets, a group for each Client: Code, Project, Kind, Due Date, Budget Hours, Hours Used, Fee, Time at Rates, Cost of Time, with the total of Hours Used, total of Fee, total of Time at Rates, total of Cost of Time.

Revenue by client

One line per client with last year's and this year's invoiced fees, and the totals.

From the query Revenue by client: Client, Sector, Invoices, Last Year, This Year, with the total of Last Year, total of This Year.

Completed projects: fee against cost of time

Completed projects with their fee and the cost of the time they took, and the totals for the period.

From the query Project profit: Finished On, Code, Project, Client, Kind, Hours, Fee, Cost of Time, with the total of Fee, total of Cost of Time.

Invoices issued, last 45 days

Each invoice of the last 45 days with its lines and its total before VAT.

From the query Recent invoice lines, a group for each Invoice: Invoice Date, Client, Description, Quantity, Unit Price, Amount, with the total of Amount.

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.