How to make a crosstab or pivot-style summary from a database
Build a crosstab query in Base by Zaweba: row headings down the side, one column heading across the top and a totalled value in each cell.
A crosstab query does it. In Base by Zaweba you choose one or more fields as row headings down the side, one field as the column heading across the top, and a value to total in the cells. Base fills in the grid, adds a Total column, and gives you the kind of summary people usually reach for a spreadsheet pivot table to make.
It is the quickest way to answer questions like "how much did each customer spend in each month" or "how many members of each type joined in each year".
Picking the three parts
Every crosstab needs the same three choices.
The row headings are what you want a line for: customers, products, towns, staff. You can use more than one, for example region and then town.
The column heading is what you want across the top. Choose something with a modest number of values, such as month, year, status or category. A column heading with hundreds of different values makes a grid nobody can read.
The value is what goes in the cells, totalled. Usually that is a sum of money or quantity, or a count of records. Each cell then holds the total for that row and that column, and the Total column adds the row up.
A worked example
Take an invoices database. Make the customer the row heading, the invoice month the column heading, and the sum of the invoice amount the value. The result is one line per customer, one column per month, and a total at the end of each line. Sorting by the total puts the biggest customers at the top.
Swap things round and the same data tells a different story. Product as the row and status as the column shows how many orders for each product are new, paid, sent or returned.
Yes and no columns
When the column heading is a yes/no field, Base labels the columns Yes and No rather than showing raw values. That is handy for things like paid and unpaid, active and lapsed, or gift aid and none, giving you two clear columns side by side.
Narrowing it down
It helps to think about which records go into the grid before you build it. If you only want one year or one region, the simplest route is to make that selection in the data first, so the grid stays small and readable. If the value you want to total doesn't exist yet as a field, a calculated field in the table or a calculated column in a query can provide it first.
Printing and sharing it
Reports can list a crosstab, so the grid can be printed or saved as a PDF in portrait or landscape A4. Landscape is often the better choice when there are many columns. To work on the numbers further, export the query to CSV, or include it in Database > Export to .xlsx, which writes a sheet per table or query with frozen headers and filters.
If you export the database as a website, the crosstab appears there as a grid on a read-only page, so people can check the latest summary from a browser.
When a grouped query is enough
Not every summary needs a crosstab. If you only want one total per customer, an ordinary query with totals (sum, grouped by customer) is simpler. Reach for the crosstab in Base by Zaweba when you want two things compared at once, one down and one across.
