Solved

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

Posted on 2014-10-29
7
10,193 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
7 Comments
 
LVL 35

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

839 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