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.
Learn Access VBA: Understand Tables, Queries, Forms, and Reports
Learn Access VBA: From Zero to Database Hero If you’ve ever opened Microsoft Access and wondered how all the pieces fit together — tables, queries, forms, and reports — this tutorial is made for you. In just a few minutes, you’ll understand how Access works behind the scenes and see how VBA (Visual Basic for […]
How to Fix Run Time Error 1004 in Excel
If you work with Microsoft Excel frequently, chances ar ling for a solution. Fortunately, this error is well-documented, and there are several ways to resolve it. In this article, we’ll explore the causes of run time error 1004, practical steps to fix it, and preventive measures to reduce the chances of it happening again. What […]
How To Escape Apostrophe In SQL Update Query
If you are looping a table with thousands of records, you’ll probably run into at least one that has an apostrophe in the field name. Like “Mike’s” or “M’cormick”, or something else. Anyway, here is one way to escape the string when you are doing your update query. Option Compare Database Sub YDriveLoop() ‘4/23/24 erik@loeblcomservices.com […]
How To Parse A Flat File In Excel VBA
In another post I demonstrated how to access a file on your computer using the MS Office Library. Here it is if you don’t know what I’m talking about. In this post, I am going to show you how to access the file and load it into your spreadsheet. I will do the same thing […]
Support these sponsors: