Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-08-22
9
Medium Priority
?
85 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
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

772 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