In my previous blog, I presented how to get data below in a reportable format in Excel. The same can be done with Access.

year;month;account;amount

2025;1;1000;167

2025;1;1010;640

2025;1;2000;15

2025;1;2000;965

2025;1;2010;278

2025;2;2010;738

2025;2;2010;973

2025;2;2010;108

2025;2;1000;947

Select new data source – from file – text file.

Browse the file. I have not created the table, so I selected the first radio button.

I selected the delimited option, as the data is separated by semicolon.

The first row is header row and the parameter is selected.

Field options are left untouched.

Access is creating a primary key field with running number.

Give the name of the new table.

Before you press finish, you can check advanced…

All the fields and data types can be reviewed. All the fields have long integer data type.

The data is neatly in Access table. Field names are taken from the file.

A simple query once data is in place.

The sum is the same as calculated with Excel.

Sales are queried by year and month.

In my previous blog I demonstrated data – text to columns and TEXTSPLIT functionalities in Excel to see the data in readable format. However, Access is suitable also for reporting purposes. Flat file was in this case easy to import into Access. I did not create the table, but the table was created while importing the data. In my test data I did not have decimals. Sometimes decimals have caused issues for me.

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.