Solved

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

Posted on 2016-08-22
9
40 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

914 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

17 Experts available now in Live!

Get 1:1 Help Now