Pivot table view in Access 2016

We inherited a spreadsheet that provides decent results. Unfortunately, data entry is a bit of a pain and maintaining master lists is troublesome compared to a code table. I figured I'd move everything to Access. But now I want to put in similar reporting, some of which are charts looking at several months of data. The chart below would be an example.

I find that Access limits the capabilities of its charts to 3 sets of data. So I figured I would just use Excel and built the appropriate queries to be able to pull the data into excel. To do this chart in Excel, it has two worksheets of data which are translated twice based on months and summations of columns and accumulation of $ throughout the month. With the final worksheet, you can then simply create the chart.

Instead of all these cross tabs and data calculations looking for workday (i.e. WorkDay.Intl), this is simply done with a couple queries in Access. That's when I hit the snag of the charts being too simplistic in Access. Every video and demo I have found states you can only have 3 data sets.

I shrugged and figured I'd use Excel since I use to do that in a different environment with SQL Server.

None of my queries are visible! After a couple hours of reading lots of blogs, help lists, etc., everyone is saying the same thing: It's a known issue that Excel can't see Access queries when they contain certain functions or multi-table/query joins. This pretty much kills 90% of the reporting which kills the whole concept of using an Access database. It's no good putting the data in if I can't get it back out.

I can't say I really understand what Microsoft is doing so maybe you can explain. I try to use charts in Access and find they are way too simplistic and minimalized so, unless I'm doing something very basic, they really don't work. I start to attempt to think outside the box and think maybe through pivot tables I might get something out of the charts. Oops! No pivot tables in Access 2016.

They are pulling the features out of Access and telling you to use Excel for your BI development. So, as a good soldier, I comply and build the appropriate queries in Access. Then when I attempt to connect to the database via Excel, I can't connect to these queries because "something" about them is not liked by Excel. Bottom line, the queries are semi-complex queries to get the data in a form that I should be able to chart with. But I find confirmation through other lists that formulas and multi-table/query joins will cause the query to be unavailable to Excel.

Then I see that I should go out and take a look at PowerBI. What I'm hearing is that I'm suppose to purchase more 3rd party products and put my data at further risk by putting it on another site on-line because Excel and Access no longer work?

I'm really not doing anything complex. These queries and charts would be considered basic in SQL Server and SSRS. I've intentionally kept things pretty simple! I'm really confused. Should we no longer be using Access and start looking for a different tool?

Pivot tables were deprecated in Access after version 2010. If you need Pivot Tables you can export the data to Excel.

Hi, I found the pivot tables essential, and I have also been looking for the graphing/charting functionality I do not see this either ?

Is there any plans to have this added back into MS Access 2016?

Regards

Julie

Please go to Access-Uservoice and vote for features you would like to see added or returned to Access. (note: don't add a suggestion unless you are sure it hasn't already been made).

In the meantime, you can export data to Excel and create pivots there. I also suggest using PowerBI for graphing.