Adding calculated columns to a query in Base by Zaweba

Tables hold the facts. Queries are where you ask questions of them, and many questions need a little arithmetic. What is each line worth with VAT? How many days late is each invoice? What does a full name look like when first and last names are joined? In Base by Zaweba you answer these with a calculated column: an Access-style expression typed in as a column of the query.

The form is a name, a colon and the expression. For example, Gross: [Quantity] * [UnitPrice] * 1.2 adds a column called Gross with each line's value including VAT at twenty per cent.

Writing an expression

Field names go in square brackets, exactly as they are spelled in the table. The usual arithmetic signs work, and so do the Access-style functions Base supports elsewhere, which means anyone used to Access expressions will feel at home. Give the column a short, clear name before the colon, since that name is what appears as the heading in the results, in reports and in exports.

Start simple and build up. Check that [Quantity] * [UnitPrice] gives the numbers you expect before multiplying by anything else. If a result looks wrong, take the expression apart until each piece is right.

Sorting and totalling

A calculated column behaves like any other column. You can sort by it, so the most valuable lines come first, and you can total it, so a grouped query shows the gross value per customer or per month. That is where calculated columns earn their keep: the sum you want isn't stored anywhere, but the query can work it out and add it up in one go.

Query column or calculated field

Base also lets you put a calculation in the table itself as a calculated field. The difference is about scope. A calculated field is part of every record, shown on every form and in every export. A calculated column belongs to one query, and appears only when that query is used.

So put lasting facts about a record in the table (a line total that is always quantity times price) and keep one-off views in a query (this year's figures with VAT, a margin for a single report). That keeps tables simple and lets each query answer its own question without cluttering the design.

Where the results go

A saved query with calculated columns can feed a report, which can group and subtotal the calculated values. It can be exported to CSV or to a sheet in an .xlsx workbook, so the person receiving it sees the gross figures already worked out. It can sit behind a form or a button too. Write the expression once and every one of those uses benefits.

Things to watch

Rates that change, such as a VAT rate, are risky to type into many queries. If the rate changes, you have to find them all. Keep calculations that depend on a rate in one saved query that others build on, or store the rate in a small table you can update.

Remember too that a calculated column can only see the fields in the query. If you need a value from a linked table, add that table to the query first. And for totals across several records, use the query's totals rather than trying to squeeze a sum into an expression on one row.