Solved

What is the best alternative for Pivot tables in Access 2013?

Posted on 2014-10-29
7
11,155 Views
Last Modified: 2014-11-05
Hi. I have a database that has several pivot tables so my end users can analyze data a number of ways. They are being upgraded to Access 2013 whether they like it or not so I need to find an alternative. I know I can export to excel but not in a pivot table format and my end users don't have the know how to build their own every time they need to do reporting. So - can access be set up to export a query to excel and format it as a pivot table? Or would linking access data to some pre-made excel pivot tables be the best solution? From what I've read linking data isn't always easy. Thoughts anyone?
0
Comment
Question by:esbyrt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 40411147
I would create a template with the pivot table defined.  Then populate the data by exporting from Access.

You could create the whole thing from scratch but that requires a lot of specific knowledge of the Excel object model which most Access developers don't have.  When I need to automate Excel, I open Excel, turn on the macro recorder and step through whatever process I need to mimic.  Then copy the generated code into Access and modify it to run from outside of Excel.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40411267
I generally use VBA and Temp tables in Access to mimic pivot tables.  If you have a good handle on VBA with Recordsets it' not that complicated.  A Temp table would hold the desired output and using VBA to step through the based data to populate it.

Just an idea.

ET
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 40411748
Remember you can "Link" the Access raw table (or query) data directly into Excel, as raw data *or as a Pivot Table*!
;-)
end users don't have the know how to build their own every time they need to do reporting.

It is best to create a backup and save the DB in the .accdb format first.
In Excel click: Data-->From Access
Select the database
Select the table
Select one of the Pivot options
Then you will get a box like this, ...
PivotCreating a Pivot in Excel can be daunting at first, ...but relatively simple after that.

In other words, the Pivot in Access was no more difficult to create than a Pivot in Excel, ...the difference is that the steps to do so are not the same.
So, like in Access, once the Pivot is set up (in Excel), the users can manipulate it in much the same way as the same Pivot in Access.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40411754
a better pic:
pivot
0
 

Author Comment

by:esbyrt
ID: 40417350
Thanks for the replies everyone. Jeffrey - Linking the data in excel as a pivot table works great but only if I remove the database password. If I leave the password on the database a box pops up asking for it but it doesn't accept it. I know my end users want the security of the password. Is there a known issue with that or do you have any idea why it's giving me grief?
Thanks!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40418364
I rarely use database passwords because they are so easy to work around.

If you don't want a person to have access to a file, then deny them the right to open/edit the file via the network privileges/permissions.

In any event, ...if the password is truly unavoidable you can try going to:
Options-->Client Settings-->Encryption Method
Select:  Use Legacy...

JeffCoachman
0
 

Author Closing Comment

by:esbyrt
ID: 40424962
Thanks for the replies everyone. I have accepted Jeffrey's as the simplest and most complete answer. I will just have to use an alternative to the password.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question