Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Pivot Table Form with Button to Drill Down to the Detail

Posted on 2010-09-19
8
Medium Priority
?
770 Views
Last Modified: 2013-11-28
I have been asked to develop a form that will be a summary of the number of moves truck drivers make during their shift. The underlying table will include the driver's id, each unit the driver moved, whether loaded or empty, and so on. Making a pivot table for this is easy, but the difficult part is that they want a button at the end of each row of the pivot table that will enable the user to drill down to the record level to see the detail of these moves. My problem is that I can't add a button to the pivot form that when pushed will pull up the transactions for that driver. Any ideas of how I can do this?
0
Comment
Question by:Ennistymon
[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
  • 5
  • 3
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33712404
You cannot do this directly, (If I am reading your request correctly), you have to use the "Collapse/expand" button/icons (the little "+" and "-" icons)

Also remember that the whole purpose of a Pivot table is to "summarize" the data. (Not displaying any real "detail" levels).

There may be some way to automate this though...

If you need to drill down from the "Move" to the "Move" details then:
1. The "Move" info and the "MoveDetails" info need to be in separate, related tables.
2. Then you can create a simple Main/subform.
This will allow you to select a "Move" in the main form and see the corresponding "MoveDetails" in the subform.

;-)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33712474
Here is a very basic sample.

Perhaps you can study it carefully and adapt it to work in your database.

;-)

JeffCoachman
Access-EEQ26484038MovesAndMoveDe.mdb
0
 

Author Comment

by:Ennistymon
ID: 33712556
Using your recommendation and modifying it a bit, I created a form from the pivot table query and also created a parameter query of the records details and made a form from that. Then I made the Pivot Form the main form and the Parameter form the sub form. The form shows a "+" sign to the left of the first column. When I click on it, the "+" runs the parameter query. The "+" changes to a "-" and drops down the detail results from the parameter query. Interestingly enough, when I go to print the detail, I only get the pivot summary results. Any idea how I can print the detail as well?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Ennistymon
ID: 33712634
For an unknown reason I can't open your file even after copying it to my machine.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33712813
I just downloaded it to myself and it worked fine.

Try downloading it again.
Also make sure that you have permissions to Add/copy, ...etc files to your destination Folder/Drive, ...etc.

Also try downloading it from another machine, or from home.

Jeff
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 1000 total points
ID: 33712846
<Interestingly enough, when I go to print the detail, I only get the pivot summary results. Any idea how I can print the detail as well?>
Again, Pivot tables are not meant to display/print "Detail" data in the manner you are requesting.

Try my form sample and see if that is an adequate workaround.

Also leave this Q open for a few more days and see in an Expert here can create something custom for you.
(Or in case I am not understanding your request...)

JeffCoachman
0
 

Author Closing Comment

by:Ennistymon
ID: 33784870
Jeff,
Thanks again for your help. Your example prompted me to think in a different way. I came up with a solution that works! Thanks again.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33786121
;-)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

609 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