Solved

Bar Graph Query needs to force a New Page with each Grouping

Posted on 2016-08-22
9
74 Views
Last Modified: 2016-08-23
Apologies for the terrible title.

I have a bar chart graph in a query in MS Access 2010.  It's sorted something like this:

Stacked Bar Charts
There are separate graphs for the groupings, and they all show up on the same page when printed.

My users really like this data, but they don't like having all the graphs together on a single page.  They would prefer if each chart had it's own page.

This is a query that's being forced to display in a chart view.  Is there any way I can do what the users want but still keep this as just a query?
0
Comment
Question by:BugHugger
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 25

Expert Comment

by:jrb1
ID: 41765898
1) Open up the report in Design view
2) Click on the header you are grouping by
3) On the Property sheet, click All
4) Look for the Force New Page property and set it to "Before Section"
0
 

Author Comment

by:BugHugger
ID: 41765961
This is a query, not a report, unfortunately.  Customizing it as a query was much easier than the report chart tool.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41766955
I'm confused...
You cannot directly display graphs in a "Query"

You may be using a query's "Data" to create the chart, ...but a Query only has a "Datasheet" view. (Not a graphical view, to see charts)

If you are in fact displaying these charts in a query, ...can you explain how you are doing this...?

Perhaps there is some confusion over the terminology here...

Going a bit further, ...Can you post a sample database?
A sample db would go a long way in making things clearer here...

JeffCoachman
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.

 

Author Comment

by:BugHugger
ID: 41767088
I probably am using the terminology wrong, so I apologize in advance.  Here is a visual representation of what I've done to clear up any confusion:

I've created a query

Query
That's based on another query.  The "Pre" query is a totals query I used to pull out only the max of a set of dates.  This second query, the one pictured here, is what I used to create the chart

2.png
Right click, select chart view, and I set up the chart the way I wanted

3.png
Back in design mode view I opened the Property Sheet

4.png
And set the default view to PivotChart

5.png
I did this so I could add this query to a few different buttons on a few different forms and it would always load as a Chart.

I've never tried to create a sample database, but there is a first time for everything.  If the above explanation doesn't help, I would be happy to try it out and upload one.

Thanks for your time with this, I greatly appreciate the help.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 41767243
That all makes sense.  I'm not sure how you are getting back multiple charts on a single page.  Is that just a form?
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 41767268
;-)
Ok, ...Now I understand, ...you are using the PivotChart view.

In order to get the output you are looking for, ...you will probably need to use a standard Report/Chart.
1. Just FYI, Pivot views have been deprecated in the more recent versions of Access
2. Pivots cannot be easily made to "Page Break", ...on the groups.

To take the guesswork out of your data, ...it would help if you posted a sample database with representative data.
0
 

Author Comment

by:BugHugger
ID: 41767364
jrb1,

Here is how I switched it to multiple charts:

In PivotChart view in the property sheet

a.png
Select drop zones

a1.png
Then group

b.png
0
 

Author Closing Comment

by:BugHugger
ID: 41767378
I guess I'll use a form with a dropdown so the users can pick which graph they want to see.  Not perfect but it'll work.  It makes me sad to hear that Microsoft is moving away from Pivot Charts.  In my opinion they're a lot more customizable than the report charts.  Bummer.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41767463
Glad you got it figured out.
;-)

You should really change the accepted post to your own solution, ...as your post shows in detail how you achieved this.
;-)

It makes me sad to hear that Microsoft is moving away from Pivot Charts.  In my opinion they're a lot more customization than the report charts.
Yes, ...but Pivots are even more flexible in Excel.
In Excel, ...the Pivots are more "Dynamic"
In Excel, Pivots are more fully "exposed" to VBA programming.
Pivots (in MS Access) were a good feature, but a bit confusing for the few people who found them useful.

If you dedicate enough time to the standard charts you will see that the possibilities are many.
Also, Reports are fully "exposed" in VBA, ...whereas Pivots were not.
(For example, you can doubleclick on a chart to open the report showing the data behind that particular chart)
Report Charts can be displayed with ("inside") the report (and as such make things like Page breaks easy and versatile), ...whereas Pivots stand on their own.
With Report Charts, ...you have easy access to modify things like: Bar colors, Data Labels, Axis options, Gridlines, Legends, Background colors, ...etc

So you are correct in that they (Pivots) are great, ...but MS did not do a good job of making the info on modifying them available to folks new to pivots.
As a result, ...a great many Pivot questions here were left to languish, due to lack of available info.

Again, I am happy that you were able to dig deep enough to get the solution you really needed.
;-)

Don't forget to ask the mods to change the selected port to your post.
;-)

JeffCoachman
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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

630 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