Solved

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

Posted on 2014-10-29
7
8,941 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 34

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA 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…

932 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now