Here is a simple data. The columns are product, region and sales.

To count how many products were sold per region, we need a pivot report.

This was an easy task to create pivot table in Excel. Region was selected as columns and product as rows. Sales is a numeric value per region and product combinations. Now we can see how many of each product was sold in each region. Pivot gives us a big picture about the data. Our sample is very limited that we could see how pivot is structured.

We have the same data in Access.

This is our table in design view, we have product and region as text columns and sales as number column.

In validation rule I have set allowed values, validation rule was entered also for products.

Select create – query wizard.

Select crosstab query wizard. This is a list of standard queries.

Select the table where the data is taken.

Row heading is selected.

Column header is selected.

The value context is the sum of sales. In this screen you can select if the sums are included in the report.

Name your query.

The results are the same as in Excel.

If you have data in Excel, you don’t have to move that to Access to have a pivot report. However, if you have the data in Access, you don’t have to move the data into Excel to have a pivot. Access includes also pivot functionality. The Pivot is called a crosstab in Access.

To test the pivot also in MySql, I created a similar table than in Access. Id is a primary key with automatic numbering. Product and region fields have checks that only allowed values are entered. This is validation rule in Access.

Then I populated the table with values.

The Pivot is reported with this SQL query:

In this query you first define the header for rows, we have the pivot with products in rows. Aliases No and So will be column headers. We are counting the sum for products in rows and regions in columns. If the region is North, then the sum for sales fields is counted. That number is presented under No column per each product. Product is not in aggregated functions, therefore we have GROUP BY product sentence.

For some reason, the SQL query does not work with Access, but it works with SQLite.  

Pivot with SQL is not so practical as with Excel. If you don’t want to transfer the data from SQL into Excel, you may have pivot in SQL editor, too. If you once download huge dataload from a system into Access, for example, might crosstab query be useful. Access is a useful tool also for pivot reporting.