Seeking 'top values' equivalent in a report

[MLH]

I have a UNION SELECT query that lists names
of people pulled from several different tables. I have
a report that prints the first 3 names in a subreport
area on main report page 1 and the rest of the names
in another subreport area on the back side (page 2).

I'm trying to figure out how to limit the output of the
two subreports to the desired first 3 names and last x
names by using the subreport object's Filter property.
All the source query supplies is the names. No num-
bering fields are available.

The total number of names shown in both subreports
varies from as few as 2 to as many as 20.

[Larry Linson]

Re: Seeking 'top values' equivalent in a report

Why don't you use a Top Values Query as the Record Source for each of the
Reports embedded in the Subreport Controls? You can use the UNION query as
the data source for those. (Just for the record, there's no such thing in
Access as a "Subreport Object".)

There are enough "quirks" with Filters that I just don't recommend solutions
based on that feature. Sorry to disappoint if you felt some compelling need
to use Filter.

Larry Linson
Microsoft Office Access MVP


"MLH" <CRCI@NorthStat e.netwrote in message
news:6vc474tpe3 20qp6tju7polsk3 ui5791bsa@4ax.c om...

>I have a UNION SELECT query that lists names
of people pulled from several different tables. I have
a report that prints the first 3 names in a subreport
area on main report page 1 and the rest of the names
in another subreport area on the back side (page 2).
>
I'm trying to figure out how to limit the output of the
two subreports to the desired first 3 names and last x
names by using the subreport object's Filter property.
All the source query supplies is the names. No num-
bering fields are available.
>
The total number of names shown in both subreports
varies from as few as 2 to as many as 20.

[MLH]

Re: Seeking 'top values' equivalent in a report

I actually ended up doing what you suggested, with some
minor mods. Although getting the 'top 3' records was a breeze,
I was having a difficult time with the 'bottom x' records. So, I
wrote the records to a table with an autonumber field. Hence,
problem solved. I appreciate the correction regarding the
subreport control. A little syntax injected into everyday conversation
now and then can be a good thing.

xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxx


On Mon, 07 Jul 2008 19:27:00 GMT, "Larry Linson"
<bouncer@localh ost.notwrote:

>Why don't you use a Top Values Query as the Record Source for each of the
>Reports embedded in the Subreport Controls? You can use the UNION query as
>the data source for those. (Just for the record, there's no such thing in
>Access as a "Subreport Object".)
>
>There are enough "quirks" with Filters that I just don't recommend solutions
>based on that feature. Sorry to disappoint if you felt some compelling need
>to use Filter.
>
Larry Linson
Microsoft Office Access MVP
>
>
>"MLH" <CRCI@NorthStat e.netwrote in message
>news:6vc474tpe 320qp6tju7polsk 3ui5791bsa@4ax. com...

>>I have a UNION SELECT query that lists names
>of people pulled from several different tables. I have
>a report that prints the first 3 names in a subreport
>area on main report page 1 and the rest of the names
>in another subreport area on the back side (page 2).
>>
>I'm trying to figure out how to limit the output of the
>two subreports to the desired first 3 names and last x
>names by using the subreport object's Filter property.
>All the source query supplies is the names. No num-
>bering fields are available.
>>
>The total number of names shown in both subreports
>varies from as few as 2 to as many as 20.

>