Solved

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

Posted on 2014-10-29
7
8,428 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

705 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

19 Experts available now in Live!

Get 1:1 Help Now