How To Convert Your Pivot Table To An Access Crosstab Query

This post is regarding how to convert your Excel pivot table to an Access format.

You can also refer to this post for more information:

https://vbahowto.com/converting-a-excel-spreadsheet-to-access-database/

Basically an Excel pivot table is an Access crosstab query, so this post goes into more detail of how to make your Access crosstab query look like what your Excel pivot table looked like.

Here is an example of the pivot table we want to convert to an Access format:

First we have to get the source data from the spreadsheet:

After this table is in Access, we can discontinue using the Excel workbook, and just have a form load the table with new data.

Here we are just filtering the data in the “tblTimeMaster” to just what we need for the report to show:

This particular crosstab query is just going to show “Hydraulics” or “VMOD” information, so we are using the query to filter first.

Now we’ll use the crosstab query to format the query like the pivot table:

Here is the design view:

I think a lot of people mess up their crosstab query formatting (including me in the beginning) because they are trying to do it too quick and don’t think about what they’re trying to show. There are only 3 parts to the crosstab query: the “Row Heading”, “Column Heading”, and “Value”.

Get a pencil and piece of paper and draw out what you want to see.

OK, back on task…

You can have multiple row headings, but only one column heading and value heading. We want “jobcode_1”, and “jobcode_2”, but since there are some blanks in “jobcode_2”, and we want to show the “(blank)” text when that happens, we customize our column heading:

“jobcode_2_mod: Nz([jobcode_2],”(blank)”)”

Basically it says that if the jobcode_2 is blank, display (blank). Access has the cool little “NZ” function which does that. In other apps you have to write it yourself.

…and of course we want a total (sum) of the hours for each person for the jobcode row.

Now onto the report:

Here is what the full report looks like in print preview:

Here’s the design view of the report:

2 things I want to point out:

1. The vertical text of the columns
2. The blank numbers show up as zeros

The text is vertical on the columns when you change this setting:

The blank numbers turn to zero based on the “format”:

You can get some ideas from this Excel related post: https://exceljet.net/custom-number-formats

Here is the database for your review

Let me know if you have any questions.

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!