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

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?
esbyrtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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
Eric ShermanAccountant/DeveloperCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
a better pic:
pivot
0
esbyrtAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
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
esbyrtAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.