Access Report Chart Across multiple group levels

[thtadthtshldntb]

I am not sure if I phrased this correctly, but I will describe what I am trying to do, or rather being asked to do.

I know how to create a chart in an access report, put it in the group header or footer and link it such that the the grouping filters the chart.

My VP wants me to create a chart that compares each invididual providers episiotomy rate (something that is done to pregnant women during delivery) to the overall divisional rate on a monthly basis.

I know how to do this with 2 charts, ie 2 different group levels. One chart goes in the date grouping level (the overall rate) and another goes in the provider grouping level (the individual provide rate).

is there a way to have this in one graph in an access report?

 

[JHB]

Can't you combine the 2 set of data into one graph?
Maybe I misunderstand what you want, then provide some more details, (sample data, printscreen of what you have and what you want)!

 

[thtadthtshldntb]

the problem is that one set of data (episiotomy rates by provider) is a subset of the other set of data (episiotomy rates for all providers).

so when you use Access Report grouping feature and tie the chart data to the parent data, the group feature at the "group by provider level" filters out the data for the other Providers.

I may just not let the perfect be the enemy of the good and do 2 separate graphs.

Or I could do something funky with logic in the query, have it produce values for each provider before it sorts/groups. I don't know.

 

[JHB]

Without your data + report setup it is difficult but I'm sure it can be done in one or other way, are you able to post your database with some sample data, (zip it), + the report name in which you've the problem?

 

[thtadthtshldntb]

sorry for not getting back sooner on this, I have like 10 or 12 differnet projects that I have to rotate between.

I did a different search and I think this is sort of what I am trying to do

http://www.access-programmers.co.uk/forums/showthread.php?t=173769

I want a chart that essentially pulls data from 2 different queries.

If that's the case and it works, then my next step is to figure out how to structure the final query so as to work with Access grouping and sorting feature

Due to corporate merger it would be more difficult to post even an empty copy of the database at the moment, there is almost no way to copy stuff out anymore in a legit fashion. They are supposed to be giving us encrytped thumb drives at some point.

I can copy/paste a copy of the query or queries that I am working with however, if that would be useful.

 

[thtadthtshldntb]

ok here's what I think is my solution, in case this helps anyone else. This may be a limitation of how I have to do things in access. And there may be a more elegant solution via VBA. But I think the following will work strictly in terms of simplicity.

My problem is that I need to create a series of graphs in an access report card for a report called Provider scorecard which basically runs several different OB/GYN datasets and gives them a comparison of their numbers to overall numbers.

So in one case I have an overall episiotomy rate and a provider specific episiotomy rate that that need to be shown on one graph. Now to take advantage of Access in built report filtering, so it generates a provider specific rate for each page, you have to link the master and child data fields from the overall report to the graph. However when you do that, due to the report filtering, it filters out all of the other providers from the overall rate, on the series 1 of the graph.

I thought using a combing query to deal with this, where

query a = overall episiotomy rate

query b = overall episiotomy rate but with the provider in it

query c = overall episiotomy rate, provider episiotomy rate and provider

using query c as the source I thought would solve the problem, but then I realized that what in fact was happening was that I was still referring to the original table and when access filtered in the final report, filtering by provider still filtered out stuff on the original table, so both rates were affected.

So my solution is to create a table that is updated every time the database opens. It will be called something like "overall stats" or whatever.

I can then refer for the overal rate to this table. And I should be able to modify query a and c to use it and get my graph. And others as need (I have to do NTSV rates on this report as well).

btw, JHB I could not at the moment post an updated version of the database (minus all data of course) because of new restrictions at work.