Solved

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

Posted on 2016-08-22
9
69 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

728 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