[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to Enable Scrolling in a MS Access Pivot Table

Posted on 2010-09-03
11
Medium Priority
?
1,503 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 1500 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
New style of hardware planning for Microsoft Exchange server.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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