Making a crosstab query of sales by month in Base by Zaweba

Long lists are hard to read. A table of a thousand sales tells you everything and shows you nothing until you summarise it. A crosstab query in Base by Zaweba does that summarising in the shape most people find easiest to read: a grid. One thing runs down the side, another runs across the top, and each cell holds a total for that pair.

Spreadsheet users will know the idea from pivot tables. Sales by product down the side and by month across the top is the classic example, but the same shape answers many other questions.

How a crosstab is built

A crosstab has three parts. The row headings go down the left, such as product or region. The column heading goes across the top, and there is just one of these, such as the month or a category. The value is what goes in the cells, totalled for each pair, such as the amount of each sale.

Base also adds a Total column at the right, so each row shows its overall figure as well as its breakdown. That last column is often the one people look at first.

Questions a crosstab answers well

Sales per product per month. Members per membership type per town. Hours per person per project. Faults per machine per week. Each of these is two ways of slicing the same records, and each reads far better as a grid than as a list.

Yes/no fields make tidy headings too. Base labels them Yes and No, so a crosstab of members by town with paid or unpaid across the top shows two clear columns, rather than ones and zeros.

Getting a readable grid

Choose a column heading with a sensible number of values. Twelve months fits on a screen; three hundred product codes across the top does not. If one side has many values, put it down the side, where rows can scroll, and keep the short list across the top.

It often helps to build the crosstab on a saved query rather than on a table. The query can pick a date range, leave out cancelled orders or add a calculated column, and the crosstab then summarises exactly the records you care about. If the base query has a parameter, you can be asked for the period each time.

Crosstabs in reports and on the website

A crosstab is not a dead end. Reports in Base can list a crosstab, so the grid prints or saves as a PDF ready for a meeting. When you export the database as a website, the website shows the crosstab as a grid as well, so people using the database in a browser see the same summary.

You can also export the result like any other query, which is useful when someone wants the grid in a spreadsheet to chart.

Common slips

The value has to be something that can be totalled in a meaningful way. Summing a price per unit across many lines produces a number nobody can use; summing a line total gives sales. If a figure looks far too big, check whether the right field is being summed.

Watch dates too. If you want months across the top, use a column that holds the month, perhaps from a calculated column in the base query, rather than the full date, or you will get one column per day.

Built with care, a crosstab turns a table of raw records into a page someone can glance at and understand, which is often the whole point of keeping the records in the first place.