Record Source vs. Row Source (Single Page Report turns into Multiple)
- From: niccovey@xxxxxxxxx
- Date: 5 Apr 2006 12:42:50 -0700
I'm only a few weeks new to Access (many thanks to these groups for
providing me with most of what I know about the program to this point)
.... kudos to the public sphere.
I have what I think is a twist on somehwat classic newbie problem -
seen at least once in a previous thread here:
http://groups.google.com/group/microsoft.public.access.reports/browse_thread/thread/ff7eb4ab4af09f6f/7c82bc37475d45a5#7c82bc37475d45a5
I've built a report that, put simply, includes both a pie chart and
what I guess you guys might call a calculated value (the calculated
value and the pie chart are both based off of the same query).
The pie chart was built from the chart wizard and is based off of a Row
Source that is a Select statement referencing said query.
The calculated value is a sum that counts 'yes' values in my query and
look something like:
=Sum(IIf([Field],1,0))
In order to calculate that value I need to make the record source for
the report the query.
PROBLEM: in this current state, the report produces multiple pages (one
per record I assume) of what I have formatted as a single report
(duplication of source problem, I know). The fix, mentioned in the
thread I reference above, is to remove the record source for the
report. Indeed, this does remove the duplication and my one page
report becomes just one page.... at that point, howevever, the record
source can no longer inform that calculated value on the report,
turning the calculated value into an error.
I can think of two solutions, neither of which I know how to do (and
neither of which are probably best):
1) If I could change the row source on the chart to include in its
statement the record source for the report, this would remove the
duplication that causes the single-multiple page issue. Presently, the
Row Source for the graph looks something like this:
SELECT [Field1],Count(*) AS [Count] FROM [Query1] GROUP BY [Field1];
or 2) If I could remove the record source for the report and add a
reference to the record source to the calculated value, that would also
remove duplication on the chart side. I don't like this option,
though, because I actually have quite a few calculated values I'd have
to change formulas for... in a test of one field, I noted it wasn't as
simple as just adding the query name before the field name... that is,
right now it looks like:
=Sum(IIf([Field],1,0))
and I can't just make it look like:
=Sum(IIf(Query1.[Field],1,0))
and call it a day....
I'm sure this entry, beyond being tediously long, is rife with rookie
statements and curious shortcomings - but I appreciate any advice this
wise access crowd can offer. I can provide more details, too, but this
seems long enough for now...
t.i.a.
Nic
.
- Prev by Date: Re: How to get a value from subreport to report
- Next by Date: Re: repeat all group footers in report footer
- Previous by thread: Re: How to get a value from subreport to report
- Next by thread: Access back end
- Index(es):
Relevant Pages
|
