Solved

How to Enable Scrolling in a MS Access Pivot Table

Posted on 2010-09-03
11
1,405 Views
Last Modified: 2012-06-21
I have a MS Access pivot table form which displays all of the results as intended, however, the far right fields extend past the default display window size (even when maximized). I cannot figure out how to scroll horizontally to view these extreme fields. Does anyone have any advice on how to scroll horizontally to view all results in a MS Access pivot table?
0
Comment
Question by:jknuce
  • 6
  • 5
11 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Buy a mouse with a "Horizontal Tilt" scroll wheel.
It works great for Excel, Access and all the new "Wide screen" apps and websites.

http://www.microsoft.com/hardware/mouseandkeyboard/features/tiltwheel.mspx

;-)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<I cannot figure out how to scroll horizontally to view these extreme fields.>
Are you saying that you do not have a horizontal scroll bar?
untitled.JPG
0
 

Author Comment

by:jknuce
Comment Utility
I see the scroll bar in your entry, but on multiple computers and using various tables of data, the bar is not observed when creating a Pivot Table form of a query in MS Access. Is there a trick to enable the horizontal bar? Is there a series of steps I can attempt to determine if it is user error? I have attached a screen shot of one of the samples that exhibits this problem.
Access.JPG
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
This appears to be a resolution issue.

The scroll bars will appear for "Data Areas" that exceed the screen.

In your screenshot, you have so many grouping levels (?!) that the Data Area is not visible.

If you first Scroll "Down" (to see the data Area), do the Horizontal scrollbars appear?

;-)

JeffCoachman
0
 

Author Comment

by:jknuce
Comment Utility
No the bar is not present and this holds true for new databses on different machines, with different installs. Attached is a ficticious databse I threw together to demonstrate the problem. Also attahced are screen shots of the table scrolled down.
Screen-Shot-1.JPG
Screen-Shot-2.JPG
No-Horizontal-Bar.zip
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I cant see what your sample is supposed to be showing me?
Linked Excel files??

Can you just post a simple query based on a static table that displays this issue?
0
 

Author Comment

by:jknuce
Comment Utility
I attached the zipped Access databse. There are no linked excel files. Can you not download the zipped database? The only form int he databse is the pivot table form. The screenshots also clearly show the horizontal bar is missing. The query returns a datasheet view. it is the pivot table view of the form that exhibits this problem.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
OK,

I downloaded the file Again.

First I will ask why you have so many fields in the PT?
You appear to have included every field in the table in the Row headings.
(Why would you need to pivot on, or summarize by, all 4 "Comment" fields?)

AFAICT, you did not (or could not) drop any fields in the "Data Area", or the Column Headings, ...thus, no horizontal scroll bars.

A PT is used to summarize data.
Under normal circumstances you will have perhaps no more than 7 fields
(Three levels in the Rows, 3 levels in the columns and one in the Data area.
...not "All" the fields in the table.
Any more than this and IMHO, the PT becomes unreadable.

An example using the data your table data would be: Annual Income By Name and State
Or:
Annual Income By Sex and Zipcode
Again, here you have one field in the rows, one field in the Columns and one numeric field to summarize.

Including all tables fields in the PT serves no real purpose that I can see.

So to address your issue of not having any scrollbars directly:
You have no Column Fields of Data Fields.

So the next question must be:
What, specifically, were  you  trying to display in this Pivot Table?

;-)

JeffCoachman
0
 

Author Comment

by:jknuce
Comment Utility
The databse provided was merely an extreme example I put together that exhibited the problem I was encountering with my true data (row fields exceeding the display window), which I could find no way to scroll to the right. I belive that this is what you and I have confirmed, which does not solve my problem. I was able to confirm that if the data is dropped in the Totals or Detail Fields (Data Area), I am able to scroll that section.  However, in this particular manner, I  use the pivot table view to display the results of a databse filtered in a particular manner (horizontally as a collapsed tree), which can be expanded if additional details are desired by the user. Using the "Data Area" for this design does not yield the desired view. Additionally, my data does not contain any values needing totaling. I believe the inability to scroll the row fields sections when they exceed the display to be a design bug of Access, but I have found a work around by limiting the fields in this particular PT. Thanks for help clarifying the problem.  I'll award full points for your assitance.
0
 

Author Closing Comment

by:jknuce
Comment Utility
This appears to be a design bug that would allow additional field rows in the table, but no manner of scrolling and viewing them.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
OK,

But to be honest, I don't think that Pivot tables were not designed to display data in the way you were requesting.
But you are correct in that if they allow you to insert a certain number of fields, then you should be allowed to scroll through those same fields...

Again, they were meant to summarize data.
So you could plot Employees in the rows and Countries in the Columns then get a sum of sales in the Data area.
Sum sales by Employee and Country

As an alternative you might want to investigate "Crosstab" queries, or even Grouped reports.

But I am glad you got  found some value in my post.

;-)

Enjoy your weekend.

Jeff
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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…

771 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

10 Experts available now in Live!

Get 1:1 Help Now