Solved

How to Enable Scrolling in a MS Access Pivot Table

Posted on 2010-09-03
11
1,462 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
[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
  • 6
  • 5
11 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33599457
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
ID: 33601176
<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
ID: 33631024
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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33633353
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
ID: 33637222
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33639878
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
ID: 33639927
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
ID: 33640959
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
ID: 33649133
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
ID: 33649157
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
ID: 33650128
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

726 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