Pie Filtering

[Guest]

I have a pie chart in the detail section of a report. The select query
(recordsource) is summing the items and is filtered by date criteria from my
report form and looks like this: ([Opened] >=#01/01/2007#) AND ([Opened] <=
#03/31/2007#). This produces multiple records for each source and looks like
this:

Source Opened Total
Equity 1/5/2007 5
Equity 1/8/2007 3
Fixed Income 1/4/2007 7
Fixed Income 1/7/2007 5

There are over ten sources besides Equity and Fixed Income and I get date
totals for each. Now this looks great for a summary report but is messed up
for a pie chart. What I want is Equity to total to 8 and Fixed Income to
total to 12 etc. and display those totals in the pie chart. To accomplish
this on the numerical reports I leave the detail section empty and use a sum
over group to total these numbers. Is there some sort of similar trick with
charts?
On a related issue, do I have to type in the Source and value in the
datasheet? This does not seem dynamic enough for a database. Because of the
filtering, some sources will have no records, and the totals will change
based on the dates selected. As always, any help will be greatly appreciated.
Thanks.

 

[Larry Linson]

I have a pie chart in the detail section of a report.

. . . To accomplish this on the numerical reports I
leave the detail section empty and use a sum
over group to total these numbers. Is there some
sort of similar trick with charts?

No "trick", but have you considered using a Totals Query?

On a related issue, do I have to type in the Source
and value in the datasheet?

Could you clarify? I don't have a clue what you are talking about.

This does not seem dynamic enough for a database.

Because of the filtering, some sources will have no
records, and the totals will change based on the dates
selected. As always, any help will be greatly appreciated.

That's why we have Forms and VBA that we can employ in creating / modifying
our Queries.

Larry Linson
Microsoft Access MVP

 

[Guest]

Larry,

Thanks for replying. I see now that the sample data was unclear. What I am
trying to demonstrate is that on January fifth, there were five Equity items
(records) and the totaling query added them up and gave me a five. To me this
is a sub total because I want the total for whatever date criteria the user
selects. If I remove the date criteria I get a total for the entire table
(three years), if I keep the dates in I get totals for individual days (even
if I don't show the dates). What I need is a total for the time period, in
this example, Q1 of 2007. I suppose I could to a total query of another total
query, but I wanted to ask if there was a better way to do this.