Solved

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

Posted on 2016-08-22
9
31 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:BugHugger
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 25

Expert Comment

by:jrb1
Comment Utility
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
Comment Utility
;-)
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

762 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

9 Experts available now in Live!

Get 1:1 Help Now