I have a report that shows the winners of a race.

The number of winner medals is based on the number of people who enter in
their specific category. There are many categories.

Is there any way to count the records for a particular category of racer and
only select those records to print on the report based on the following
criteria?

If there are between 1-5 racers, I'd select one record to print (the record
with the lowest FinishTime))
If there are between 6-10 racers, I'd select 2 records to print (the records
with the lowest and second lowest FinishTime)

I am not at all familiar with Visual Basic so am hoping someone can offer a
suggestion on how to do this using the Access queries and macros.

Thanks

You will need a ranking query to rank the racers in each category

This might work.

SELECT RA.RunnerID, Count(RB.FinishTime) as Rank,
FROM Races as RA INNER JOIN Races as RB
ON RA.Category = RB.Category
AND RA.FinishTime > RB.FinishTime
GROUP BY RA.RunnerID
HAVING Count(RB.FinishTime) <=
(SELECT Count(*) FROM Races as RC WHERE RC.Category = Ra.Category)\5

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

pauld wrote:
> I have a report that shows the winners of a race.
>
> The number of winner medals is based on the number of people who enter in
> their specific category. There are many categories.
>
> Is there any way to count the records for a particular category of racer and
> only select those records to print on the report based on the following
> criteria?
>
> If there are between 1-5 racers, I'd select one record to print (the record
> with the lowest FinishTime))
> If there are between 6-10 racers, I'd select 2 records to print (the records
> with the lowest and second lowest FinishTime)
>
> I am not at all familiar with Visual Basic so am hoping someone can offer a
> suggestion on how to do this using the Access queries and macros.
>
> Thanks

Thanks for your response,

I use a simple query sorting on finish time from lowest to highest to
determine who is first, second, third, etc. That works just fine.

What I'm trying to do is limit the number of winners I show on the report.
The number of winners is based on the number of entries. If the number of
entries is 1-5, I award a trophy to one winnner. If the number of entries is
6-10, I award 2 trophies, etc

How do I limit the number of records I show based on the count of the
entries for that race?

"John Spencer MVP" wrote:

> You will need a ranking query to rank the racers in each category
>
> This might work.
>
> SELECT RA.RunnerID, Count(RB.FinishTime) as Rank,
> FROM Races as RA INNER JOIN Races as RB
> ON RA.Category = RB.Category
> AND RA.FinishTime > RB.FinishTime
> GROUP BY RA.RunnerID
> HAVING Count(RB.FinishTime) <=
> (SELECT Count(*) FROM Races as RC WHERE RC.Category = Ra.Category)\5
>
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
>
> pauld wrote:
> > I have a report that shows the winners of a race.
> >
> > The number of winner medals is based on the number of people who enter in
> > their specific category. There are many categories.
> >
> > Is there any way to count the records for a particular category of racer and
> > only select those records to print on the report based on the following
> > criteria?
> >
> > If there are between 1-5 racers, I'd select one record to print (the record
> > with the lowest FinishTime))
> > If there are between 6-10 racers, I'd select 2 records to print (the records
> > with the lowest and second lowest FinishTime)
> >
> > I am not at all familiar with Visual Basic so am hoping someone can offer a
> > suggestion on how to do this using the Access queries and macros.
> >
> > Thanks
>

Firstly don't bother sorting the query unless you are also using it
elsewhere, e.g. as the RecordSource for a form; otherwise use the report's
internal sorting and grouping mechanism to sort it. Do so even if you do
sort the query. Then in the report do the following:

1. Add an unbound text box txtTotalCount to the report header section, with
a ControlSource property of =Count(*). Set its Visible property to False
(No).

2. Add an unbound text box txtCount to the detail section, with a
ControlSource property of =1. Set its RunningSum property to 'Over All' and
its Visible property to False (No).

3. In the detail section's Format event procedure put:

Dim n As Integer

n = Me.txtTotalCount
Cancel = Me.txtCount > ((n - 1) \ 5) + 1

Note that a simple query, and consequently a report using the above will
not handle ties for the final relevant place, however, as only one of the
runners with equal times will appear in the report, so if there are 8 runners
and one is the overall winner but two tie with equal second place times, one
of the latter would unjustifiably be excluded from the report. To cater for
that you'd need to use a query which ranks the places and gives equal ranks
to ties, e.g.

SELECT R1.*,
(SELECT COUNT(*)
FROM RaceResults AS R2
WHERE R2.Race = R1.Race
AND R2.Time < R1.Time)+1 AS Position
FROM RaceResults AS R1
WHERE Race = [Enter Race:];

You'd then sort the report by Position. You'd then have a text box bound to
the position column in the detail section rather than the unbound txtCount
control, and the code in the detail sections' Format event would be:

Dim n As Integer

n = Me.txtTotalCount
Cancel = Me.Position > ((n - 1) \ 5) + 1

You refer to macros, and say that you are not be familiar with entering VBA
code in the event procedures of reports or forms. This is how its done:

Select the section by clicking on its title bar in report design view and
open its properties sheet if its not already open. Then select the Format
event property in the properties sheet. Click on the 'build' button; that's
the one on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the first and
last lines already in place. Enter the lines of code between these two
existing lines.

Ken Sheridan
Stafford, England

"pauld" wrote:

> Thanks for your response,
>
> I use a simple query sorting on finish time from lowest to highest to
> determine who is first, second, third, etc. That works just fine.
>
> What I'm trying to do is limit the number of winners I show on the report.
> The number of winners is based on the number of entries. If the number of
> entries is 1-5, I award a trophy to one winnner. If the number of entries is
> 6-10, I award 2 trophies, etc
>
> How do I limit the number of records I show based on the count of the
> entries for that race?
>
> "John Spencer MVP" wrote:
>
> > You will need a ranking query to rank the racers in each category
> >
> > This might work.
> >
> > SELECT RA.RunnerID, Count(RB.FinishTime) as Rank,
> > FROM Races as RA INNER JOIN Races as RB
> > ON RA.Category = RB.Category
> > AND RA.FinishTime > RB.FinishTime
> > GROUP BY RA.RunnerID
> > HAVING Count(RB.FinishTime) <=
> > (SELECT Count(*) FROM Races as RC WHERE RC.Category = Ra.Category)\5
> >
> > John Spencer
> > Access MVP 2002-2005, 2007-2009
> > The Hilltop Institute
> > University of Maryland Baltimore County
> >
> > pauld wrote:
> > > I have a report that shows the winners of a race.
> > >
> > > The number of winner medals is based on the number of people who enter in
> > > their specific category. There are many categories.
> > >
> > > Is there any way to count the records for a particular category of racer and
> > > only select those records to print on the report based on the following
> > > criteria?
> > >
> > > If there are between 1-5 racers, I'd select one record to print (the record
> > > with the lowest FinishTime))
> > > If there are between 6-10 racers, I'd select 2 records to print (the records
> > > with the lowest and second lowest FinishTime)
> > >
> > > I am not at all familiar with Visual Basic so am hoping someone can offer a
> > > suggestion on how to do this using the Access queries and macros.
> > >
> > > Thanks
> >

Thanks.

I don't think I'm explaining myself correctly...so at the risk of writing a
book, let me expand a bit...

We have criteria that says if 1-5 people enter the race, we will hand out
one trophy; if 6-10 people enter the race, we will hand out 2 trophies,
etc...all the way up to if more than 25 people enter the race, we will hand
out 6 trophies.

There are many different categories wihin the race so I print a report for
each category showing the winner(s) of that category. I sort based on Finish
Time (ascending) so all of the finishers are listed in the correct order.

The problem is I currently have to print all of entrants within a specific
category on each report and manually mark-out the entries that are not
getting trophies.

What I'd like to be able to do is have the number of entries control how
many records I actually print...so that I don't have any manual mark-outs on
the report.

I'd like Access to count the entries (assume it finds 8 entries which means
we will award 2 trophies) and then print only 2 records on the report.

It's the issue of counting the records for the category and having it drive
the number of records to print that is my question.

Hope this helps



"Ken Sheridan" wrote:

> Firstly don't bother sorting the query unless you are also using it
> elsewhere, e.g. as the RecordSource for a form; otherwise use the report's
> internal sorting and grouping mechanism to sort it. Do so even if you do
> sort the query. Then in the report do the following:
>
> 1. Add an unbound text box txtTotalCount to the report header section, with
> a ControlSource property of =Count(*). Set its Visible property to False
> (No).
>
> 2. Add an unbound text box txtCount to the detail section, with a
> ControlSource property of =1. Set its RunningSum property to 'Over All' and
> its Visible property to False (No).
>
> 3. In the detail section's Format event procedure put:
>
> Dim n As Integer
>
> n = Me.txtTotalCount
> Cancel = Me.txtCount > ((n - 1) \ 5) + 1
>
> Note that a simple query, and consequently a report using the above will
> not handle ties for the final relevant place, however, as only one of the
> runners with equal times will appear in the report, so if there are 8 runners
> and one is the overall winner but two tie with equal second place times, one
> of the latter would unjustifiably be excluded from the report. To cater for
> that you'd need to use a query which ranks the places and gives equal ranks
> to ties, e.g.
>
> SELECT R1.*,
> (SELECT COUNT(*)
> FROM RaceResults AS R2
> WHERE R2.Race = R1.Race
> AND R2.Time < R1.Time)+1 AS Position
> FROM RaceResults AS R1
> WHERE Race = [Enter Race:];
>
> You'd then sort the report by Position. You'd then have a text box bound to
> the position column in the detail section rather than the unbound txtCount
> control, and the code in the detail sections' Format event would be:
>
> Dim n As Integer
>
> n = Me.txtTotalCount
> Cancel = Me.Position > ((n - 1) \ 5) + 1
>
> You refer to macros, and say that you are not be familiar with entering VBA
> code in the event procedures of reports or forms. This is how its done:
>
> Select the section by clicking on its title bar in report design view and
> open its properties sheet if its not already open. Then select the Format
> event property in the properties sheet. Click on the 'build' button; that's
> the one on the right with 3 dots. Select 'Code Builder' in the dialogue, and
> click OK. The VBA window will open at the event procedure with the first and
> last lines already in place. Enter the lines of code between these two
> existing lines.
>
> Ken Sheridan
> Stafford, England
>
> "pauld" wrote:
>
> > Thanks for your response,
> >
> > I use a simple query sorting on finish time from lowest to highest to
> > determine who is first, second, third, etc. That works just fine.
> >
> > What I'm trying to do is limit the number of winners I show on the report.
> > The number of winners is based on the number of entries. If the number of
> > entries is 1-5, I award a trophy to one winnner. If the number of entries is
> > 6-10, I award 2 trophies, etc
> >
> > How do I limit the number of records I show based on the count of the
> > entries for that race?
> >
> > "John Spencer MVP" wrote:
> >
> > > You will need a ranking query to rank the racers in each category
> > >
> > > This might work.
> > >
> > > SELECT RA.RunnerID, Count(RB.FinishTime) as Rank,
> > > FROM Races as RA INNER JOIN Races as RB
> > > ON RA.Category = RB.Category
> > > AND RA.FinishTime > RB.FinishTime
> > > GROUP BY RA.RunnerID
> > > HAVING Count(RB.FinishTime) <=
> > > (SELECT Count(*) FROM Races as RC WHERE RC.Category = Ra.Category)\5
> > >
> > > John Spencer
> > > Access MVP 2002-2005, 2007-2009
> > > The Hilltop Institute
> > > University of Maryland Baltimore County
> > >
> > > pauld wrote:
> > > > I have a report that shows the winners of a race.
> > > >
> > > > The number of winner medals is based on the number of people who enter in
> > > > their specific category. There are many categories.
> > > >
> > > > Is there any way to count the records for a particular category of racer and
> > > > only select those records to print on the report based on the following
> > > > criteria?
> > > >
> > > > If there are between 1-5 racers, I'd select one record to print (the record
> > > > with the lowest FinishTime))
> > > > If there are between 6-10 racers, I'd select 2 records to print (the records
> > > > with the lowest and second lowest FinishTime)
> > > >
> > > > I am not at all familiar with Visual Basic so am hoping someone can offer a
> > > > suggestion on how to do this using the Access queries and macros.
> > > >
> > > > Thanks
> > >
>

Did you try the suggested method? I may not be reading the code correctly,
but I think the Cancel statement stops the printing when the requisite
number of names have been printed. It don't think it stops after a maximum
of 6 trophies, but I believe that was an added detail in your most recent
posting.

As for categories, can you use the report's Sorting and Grouping to group by
category? If so, use the group header and footer instead of the report
header and footer, and set the Running Sum to Over Group instead of Over
All.

"pauld" <(E-Mail Removed)> wrote in message
news:6D75D2CD-976F-4E01-8B52-(E-Mail Removed)...
> Thanks.
>
> I don't think I'm explaining myself correctly...so at the risk of writing
> a
> book, let me expand a bit...
>
> We have criteria that says if 1-5 people enter the race, we will hand out
> one trophy; if 6-10 people enter the race, we will hand out 2 trophies,
> etc...all the way up to if more than 25 people enter the race, we will
> hand
> out 6 trophies.
>
> There are many different categories wihin the race so I print a report for
> each category showing the winner(s) of that category. I sort based on
> Finish
> Time (ascending) so all of the finishers are listed in the correct order.
>
> The problem is I currently have to print all of entrants within a specific
> category on each report and manually mark-out the entries that are not
> getting trophies.
>
> What I'd like to be able to do is have the number of entries control how
> many records I actually print...so that I don't have any manual mark-outs
> on
> the report.
>
> I'd like Access to count the entries (assume it finds 8 entries which
> means
> we will award 2 trophies) and then print only 2 records on the report.
>
> It's the issue of counting the records for the category and having it
> drive
> the number of records to print that is my question.
>
> Hope this helps
>
>
>
> "Ken Sheridan" wrote:
>
>> Firstly don't bother sorting the query unless you are also using it
>> elsewhere, e.g. as the RecordSource for a form; otherwise use the
>> report's
>> internal sorting and grouping mechanism to sort it. Do so even if you do
>> sort the query. Then in the report do the following:
>>
>> 1. Add an unbound text box txtTotalCount to the report header section,
>> with
>> a ControlSource property of =Count(*). Set its Visible property to False
>> (No).
>>
>> 2. Add an unbound text box txtCount to the detail section, with a
>> ControlSource property of =1. Set its RunningSum property to 'Over All'
>> and
>> its Visible property to False (No).
>>
>> 3. In the detail section's Format event procedure put:
>>
>> Dim n As Integer
>>
>> n = Me.txtTotalCount
>> Cancel = Me.txtCount > ((n - 1) \ 5) + 1
>>
>> Note that a simple query, and consequently a report using the above will
>> not handle ties for the final relevant place, however, as only one of the
>> runners with equal times will appear in the report, so if there are 8
>> runners
>> and one is the overall winner but two tie with equal second place times,
>> one
>> of the latter would unjustifiably be excluded from the report. To cater
>> for
>> that you'd need to use a query which ranks the places and gives equal
>> ranks
>> to ties, e.g.
>>
>> SELECT R1.*,
>> (SELECT COUNT(*)
>> FROM RaceResults AS R2
>> WHERE R2.Race = R1.Race
>> AND R2.Time < R1.Time)+1 AS Position
>> FROM RaceResults AS R1
>> WHERE Race = [Enter Race:];
>>
>> You'd then sort the report by Position. You'd then have a text box bound
>> to
>> the position column in the detail section rather than the unbound
>> txtCount
>> control, and the code in the detail sections' Format event would be:
>>
>> Dim n As Integer
>>
>> n = Me.txtTotalCount
>> Cancel = Me.Position > ((n - 1) \ 5) + 1
>>
>> You refer to macros, and say that you are not be familiar with entering
>> VBA
>> code in the event procedures of reports or forms. This is how its done:
>>
>> Select the section by clicking on its title bar in report design view and
>> open its properties sheet if its not already open. Then select the
>> Format
>> event property in the properties sheet. Click on the 'build' button;
>> that's
>> the one on the right with 3 dots. Select 'Code Builder' in the dialogue,
>> and
>> click OK. The VBA window will open at the event procedure with the first
>> and
>> last lines already in place. Enter the lines of code between these two
>> existing lines.
>>
>> Ken Sheridan
>> Stafford, England
>>
>> "pauld" wrote:
>>
>> > Thanks for your response,
>> >
>> > I use a simple query sorting on finish time from lowest to highest to
>> > determine who is first, second, third, etc. That works just fine.
>> >
>> > What I'm trying to do is limit the number of winners I show on the
>> > report.
>> > The number of winners is based on the number of entries. If the number
>> > of
>> > entries is 1-5, I award a trophy to one winnner. If the number of
>> > entries is
>> > 6-10, I award 2 trophies, etc
>> >
>> > How do I limit the number of records I show based on the count of the
>> > entries for that race?
>> >
>> > "John Spencer MVP" wrote:
>> >
>> > > You will need a ranking query to rank the racers in each category
>> > >
>> > > This might work.
>> > >
>> > > SELECT RA.RunnerID, Count(RB.FinishTime) as Rank,
>> > > FROM Races as RA INNER JOIN Races as RB
>> > > ON RA.Category = RB.Category
>> > > AND RA.FinishTime > RB.FinishTime
>> > > GROUP BY RA.RunnerID
>> > > HAVING Count(RB.FinishTime) <=
>> > > (SELECT Count(*) FROM Races as RC WHERE RC.Category =
>> > > Ra.Category)\5
>> > >
>> > > John Spencer
>> > > Access MVP 2002-2005, 2007-2009
>> > > The Hilltop Institute
>> > > University of Maryland Baltimore County
>> > >
>> > > pauld wrote:
>> > > > I have a report that shows the winners of a race.
>> > > >
>> > > > The number of winner medals is based on the number of people who
>> > > > enter in
>> > > > their specific category. There are many categories.
>> > > >
>> > > > Is there any way to count the records for a particular category of
>> > > > racer and
>> > > > only select those records to print on the report based on the
>> > > > following
>> > > > criteria?
>> > > >
>> > > > If there are between 1-5 racers, I'd select one record to print
>> > > > (the record
>> > > > with the lowest FinishTime))
>> > > > If there are between 6-10 racers, I'd select 2 records to print
>> > > > (the records
>> > > > with the lowest and second lowest FinishTime)
>> > > >
>> > > > I am not at all familiar with Visual Basic so am hoping someone can
>> > > > offer a
>> > > > suggestion on how to do this using the Access queries and macros.
>> > > >
>> > > > Thanks
>> > >
>>

There are 50 different categories in the race.

I need to print one page per category (long story) so sorting/grouping isn't
really an option.

Each page has at least one winner up to a maximum of 6 winners. The number
of entries drives the number of winners who will get a trophy (and therefore
have their names printed on the report).

I really don't understand the suggested code and how it is supposed to work.
I know this forum isn't a school, but I'd sure like to know what I'm doing.
Any chance you could tell me what the code is doing step by step?

Thanks.



"BruceM" wrote:

> Did you try the suggested method? I may not be reading the code correctly,
> but I think the Cancel statement stops the printing when the requisite
> number of names have been printed. It don't think it stops after a maximum
> of 6 trophies, but I believe that was an added detail in your most recent
> posting.
>
> As for categories, can you use the report's Sorting and Grouping to group by
> category? If so, use the group header and footer instead of the report
> header and footer, and set the Running Sum to Over Group instead of Over
> All.
>
> "pauld" <(E-Mail Removed)> wrote in message
> news:6D75D2CD-976F-4E01-8B52-(E-Mail Removed)...
> > Thanks.
> >
> > I don't think I'm explaining myself correctly...so at the risk of writing
> > a
> > book, let me expand a bit...
> >
> > We have criteria that says if 1-5 people enter the race, we will hand out
> > one trophy; if 6-10 people enter the race, we will hand out 2 trophies,
> > etc...all the way up to if more than 25 people enter the race, we will
> > hand
> > out 6 trophies.
> >
> > There are many different categories wihin the race so I print a report for
> > each category showing the winner(s) of that category. I sort based on
> > Finish
> > Time (ascending) so all of the finishers are listed in the correct order.
> >
> > The problem is I currently have to print all of entrants within a specific
> > category on each report and manually mark-out the entries that are not
> > getting trophies.
> >
> > What I'd like to be able to do is have the number of entries control how
> > many records I actually print...so that I don't have any manual mark-outs
> > on
> > the report.
> >
> > I'd like Access to count the entries (assume it finds 8 entries which
> > means
> > we will award 2 trophies) and then print only 2 records on the report.
> >
> > It's the issue of counting the records for the category and having it
> > drive
> > the number of records to print that is my question.
> >
> > Hope this helps
> >
> >
> >
> > "Ken Sheridan" wrote:
> >
> >> Firstly don't bother sorting the query unless you are also using it
> >> elsewhere, e.g. as the RecordSource for a form; otherwise use the
> >> report's
> >> internal sorting and grouping mechanism to sort it. Do so even if you do
> >> sort the query. Then in the report do the following:
> >>
> >> 1. Add an unbound text box txtTotalCount to the report header section,
> >> with
> >> a ControlSource property of =Count(*). Set its Visible property to False
> >> (No).
> >>
> >> 2. Add an unbound text box txtCount to the detail section, with a
> >> ControlSource property of =1. Set its RunningSum property to 'Over All'
> >> and
> >> its Visible property to False (No).
> >>
> >> 3. In the detail section's Format event procedure put:
> >>
> >> Dim n As Integer
> >>
> >> n = Me.txtTotalCount
> >> Cancel = Me.txtCount > ((n - 1) \ 5) + 1
> >>
> >> Note that a simple query, and consequently a report using the above will
> >> not handle ties for the final relevant place, however, as only one of the
> >> runners with equal times will appear in the report, so if there are 8
> >> runners
> >> and one is the overall winner but two tie with equal second place times,
> >> one
> >> of the latter would unjustifiably be excluded from the report. To cater
> >> for
> >> that you'd need to use a query which ranks the places and gives equal
> >> ranks
> >> to ties, e.g.
> >>
> >> SELECT R1.*,
> >> (SELECT COUNT(*)
> >> FROM RaceResults AS R2
> >> WHERE R2.Race = R1.Race
> >> AND R2.Time < R1.Time)+1 AS Position
> >> FROM RaceResults AS R1
> >> WHERE Race = [Enter Race:];
> >>
> >> You'd then sort the report by Position. You'd then have a text box bound
> >> to
> >> the position column in the detail section rather than the unbound
> >> txtCount
> >> control, and the code in the detail sections' Format event would be:
> >>
> >> Dim n As Integer
> >>
> >> n = Me.txtTotalCount
> >> Cancel = Me.Position > ((n - 1) \ 5) + 1
> >>
> >> You refer to macros, and say that you are not be familiar with entering
> >> VBA
> >> code in the event procedures of reports or forms. This is how its done:
> >>
> >> Select the section by clicking on its title bar in report design view and
> >> open its properties sheet if its not already open. Then select the
> >> Format
> >> event property in the properties sheet. Click on the 'build' button;
> >> that's
> >> the one on the right with 3 dots. Select 'Code Builder' in the dialogue,
> >> and
> >> click OK. The VBA window will open at the event procedure with the first
> >> and
> >> last lines already in place. Enter the lines of code between these two
> >> existing lines.
> >>
> >> Ken Sheridan
> >> Stafford, England
> >>
> >> "pauld" wrote:
> >>
> >> > Thanks for your response,
> >> >
> >> > I use a simple query sorting on finish time from lowest to highest to
> >> > determine who is first, second, third, etc. That works just fine.
> >> >
> >> > What I'm trying to do is limit the number of winners I show on the
> >> > report.
> >> > The number of winners is based on the number of entries. If the number
> >> > of
> >> > entries is 1-5, I award a trophy to one winnner. If the number of
> >> > entries is
> >> > 6-10, I award 2 trophies, etc
> >> >
> >> > How do I limit the number of records I show based on the count of the
> >> > entries for that race?
> >> >
> >> > "John Spencer MVP" wrote:
> >> >
> >> > > You will need a ranking query to rank the racers in each category
> >> > >
> >> > > This might work.
> >> > >
> >> > > SELECT RA.RunnerID, Count(RB.FinishTime) as Rank,
> >> > > FROM Races as RA INNER JOIN Races as RB
> >> > > ON RA.Category = RB.Category
> >> > > AND RA.FinishTime > RB.FinishTime
> >> > > GROUP BY RA.RunnerID
> >> > > HAVING Count(RB.FinishTime) <=
> >> > > (SELECT Count(*) FROM Races as RC WHERE RC.Category =
> >> > > Ra.Category)\5
> >> > >
> >> > > John Spencer
> >> > > Access MVP 2002-2005, 2007-2009
> >> > > The Hilltop Institute
> >> > > University of Maryland Baltimore County
> >> > >
> >> > > pauld wrote:
> >> > > > I have a report that shows the winners of a race.
> >> > > >
> >> > > > The number of winner medals is based on the number of people who
> >> > > > enter in
> >> > > > their specific category. There are many categories.
> >> > > >
> >> > > > Is there any way to count the records for a particular category of
> >> > > > racer and
> >> > > > only select those records to print on the report based on the
> >> > > > following
> >> > > > criteria?
> >> > > >
> >> > > > If there are between 1-5 racers, I'd select one record to print
> >> > > > (the record
> >> > > > with the lowest FinishTime))
> >> > > > If there are between 6-10 racers, I'd select 2 records to print
> >> > > > (the records
> >> > > > with the lowest and second lowest FinishTime)
> >> > > >
> >> > > > I am not at all familiar with Visual Basic so am hoping someone can
> >> > > > offer a
> >> > > > suggestion on how to do this using the Access queries and macros.
> >> > > >
> >> > > > Thanks
> >> > >
> >>
>
>
>

Why is sorting/grouping not an option? Grouping by category, while forcing
a new page in the group header and footer, will give you a page (or more, if
necessary) for each category.

Ken's code and method, as I understand it, creates a text box
(txtTotalCount) in the header that shows the total number of records, and a
text box (txtCount) in the detail section that keeps a running sum of the
number of records. Then this code appears in the Format event for the
Detail section:

Dim n As Integer

n = Me.txtTotalCount
Cancel = Me.txtCount > ((n - 1) \ 5) + 1

If the total number of records is 11, plug that into the formula instead of
"n":

((11-1) \ 5) + 1

(10 \ 5) + 1

2 + 1

3

When Me.txtCount (the running sum text box) shows a value greater than 3,
the Format event cancels, which is to say it stops, and the report shows
only the number of records specified by the formula. Perhaps you will need
to tweak the formula. For instance, maybe >= rather than >, but that should
be an easy enough adjustment if the rest of it works. The SQL Ken showed is
to handle ties in case more than one person has the same time for first or
second place. You would use that as the Record Source for the report.

I see no real alternative to using the report's sorting and grouping. It is
possible to group in a query, and to select some number of top values. It
may even be possible to adjust the number of top values depending on the
number of entrants, but such a query structure is beyond what I can
illustrate.


"pauld" <(E-Mail Removed)> wrote in message
news:C8EC9338-5D46-44E9-AD75-(E-Mail Removed)...
> There are 50 different categories in the race.
>
> I need to print one page per category (long story) so sorting/grouping
> isn't
> really an option.
>
> Each page has at least one winner up to a maximum of 6 winners. The number
> of entries drives the number of winners who will get a trophy (and
> therefore
> have their names printed on the report).
>
> I really don't understand the suggested code and how it is supposed to
> work.
> I know this forum isn't a school, but I'd sure like to know what I'm
> doing.
> Any chance you could tell me what the code is doing step by step?
>
> Thanks.
>
>
>
> "BruceM" wrote:
>
>> Did you try the suggested method? I may not be reading the code
>> correctly,
>> but I think the Cancel statement stops the printing when the requisite
>> number of names have been printed. It don't think it stops after a
>> maximum
>> of 6 trophies, but I believe that was an added detail in your most recent
>> posting.
>>
>> As for categories, can you use the report's Sorting and Grouping to group
>> by
>> category? If so, use the group header and footer instead of the report
>> header and footer, and set the Running Sum to Over Group instead of Over
>> All.
>>
>> "pauld" <(E-Mail Removed)> wrote in message
>> news:6D75D2CD-976F-4E01-8B52-(E-Mail Removed)...
>> > Thanks.
>> >
>> > I don't think I'm explaining myself correctly...so at the risk of
>> > writing
>> > a
>> > book, let me expand a bit...
>> >
>> > We have criteria that says if 1-5 people enter the race, we will hand
>> > out
>> > one trophy; if 6-10 people enter the race, we will hand out 2 trophies,
>> > etc...all the way up to if more than 25 people enter the race, we will
>> > hand
>> > out 6 trophies.
>> >
>> > There are many different categories wihin the race so I print a report
>> > for
>> > each category showing the winner(s) of that category. I sort based on
>> > Finish
>> > Time (ascending) so all of the finishers are listed in the correct
>> > order.
>> >
>> > The problem is I currently have to print all of entrants within a
>> > specific
>> > category on each report and manually mark-out the entries that are not
>> > getting trophies.
>> >
>> > What I'd like to be able to do is have the number of entries control
>> > how
>> > many records I actually print...so that I don't have any manual
>> > mark-outs
>> > on
>> > the report.
>> >
>> > I'd like Access to count the entries (assume it finds 8 entries which
>> > means
>> > we will award 2 trophies) and then print only 2 records on the report.
>> >
>> > It's the issue of counting the records for the category and having it
>> > drive
>> > the number of records to print that is my question.
>> >
>> > Hope this helps
>> >
>> >
>> >
>> > "Ken Sheridan" wrote:
>> >
>> >> Firstly don't bother sorting the query unless you are also using it
>> >> elsewhere, e.g. as the RecordSource for a form; otherwise use the
>> >> report's
>> >> internal sorting and grouping mechanism to sort it. Do so even if you
>> >> do
>> >> sort the query. Then in the report do the following:
>> >>
>> >> 1. Add an unbound text box txtTotalCount to the report header
>> >> section,
>> >> with
>> >> a ControlSource property of =Count(*). Set its Visible property to
>> >> False
>> >> (No).
>> >>
>> >> 2. Add an unbound text box txtCount to the detail section, with a
>> >> ControlSource property of =1. Set its RunningSum property to 'Over
>> >> All'
>> >> and
>> >> its Visible property to False (No).
>> >>
>> >> 3. In the detail section's Format event procedure put:
>> >>
>> >> Dim n As Integer
>> >>
>> >> n = Me.txtTotalCount
>> >> Cancel = Me.txtCount > ((n - 1) \ 5) + 1
>> >>
>> >> Note that a simple query, and consequently a report using the above
>> >> will
>> >> not handle ties for the final relevant place, however, as only one of
>> >> the
>> >> runners with equal times will appear in the report, so if there are 8
>> >> runners
>> >> and one is the overall winner but two tie with equal second place
>> >> times,
>> >> one
>> >> of the latter would unjustifiably be excluded from the report. To
>> >> cater
>> >> for
>> >> that you'd need to use a query which ranks the places and gives equal
>> >> ranks
>> >> to ties, e.g.
>> >>
>> >> SELECT R1.*,
>> >> (SELECT COUNT(*)
>> >> FROM RaceResults AS R2
>> >> WHERE R2.Race = R1.Race
>> >> AND R2.Time < R1.Time)+1 AS Position
>> >> FROM RaceResults AS R1
>> >> WHERE Race = [Enter Race:];
>> >>
>> >> You'd then sort the report by Position. You'd then have a text box
>> >> bound
>> >> to
>> >> the position column in the detail section rather than the unbound
>> >> txtCount
>> >> control, and the code in the detail sections' Format event would be:
>> >>
>> >> Dim n As Integer
>> >>
>> >> n = Me.txtTotalCount
>> >> Cancel = Me.Position > ((n - 1) \ 5) + 1
>> >>
>> >> You refer to macros, and say that you are not be familiar with
>> >> entering
>> >> VBA
>> >> code in the event procedures of reports or forms. This is how its
>> >> done:
>> >>
>> >> Select the section by clicking on its title bar in report design view
>> >> and
>> >> open its properties sheet if its not already open. Then select the
>> >> Format
>> >> event property in the properties sheet. Click on the 'build' button;
>> >> that's
>> >> the one on the right with 3 dots. Select 'Code Builder' in the
>> >> dialogue,
>> >> and
>> >> click OK. The VBA window will open at the event procedure with the
>> >> first
>> >> and
>> >> last lines already in place. Enter the lines of code between these
>> >> two
>> >> existing lines.
>> >>
>> >> Ken Sheridan
>> >> Stafford, England
>> >>
>> >> "pauld" wrote:
>> >>
>> >> > Thanks for your response,
>> >> >
>> >> > I use a simple query sorting on finish time from lowest to highest
>> >> > to
>> >> > determine who is first, second, third, etc. That works just fine.
>> >> >
>> >> > What I'm trying to do is limit the number of winners I show on the
>> >> > report.
>> >> > The number of winners is based on the number of entries. If the
>> >> > number
>> >> > of
>> >> > entries is 1-5, I award a trophy to one winnner. If the number of
>> >> > entries is
>> >> > 6-10, I award 2 trophies, etc
>> >> >
>> >> > How do I limit the number of records I show based on the count of
>> >> > the
>> >> > entries for that race?
>> >> >
>> >> > "John Spencer MVP" wrote:
>> >> >
>> >> > > You will need a ranking query to rank the racers in each category
>> >> > >
>> >> > > This might work.
>> >> > >
>> >> > > SELECT RA.RunnerID, Count(RB.FinishTime) as Rank,
>> >> > > FROM Races as RA INNER JOIN Races as RB
>> >> > > ON RA.Category = RB.Category
>> >> > > AND RA.FinishTime > RB.FinishTime
>> >> > > GROUP BY RA.RunnerID
>> >> > > HAVING Count(RB.FinishTime) <=
>> >> > > (SELECT Count(*) FROM Races as RC WHERE RC.Category =
>> >> > > Ra.Category)\5
>> >> > >
>> >> > > John Spencer
>> >> > > Access MVP 2002-2005, 2007-2009
>> >> > > The Hilltop Institute
>> >> > > University of Maryland Baltimore County
>> >> > >
>> >> > > pauld wrote:
>> >> > > > I have a report that shows the winners of a race.
>> >> > > >
>> >> > > > The number of winner medals is based on the number of people who
>> >> > > > enter in
>> >> > > > their specific category. There are many categories.
>> >> > > >
>> >> > > > Is there any way to count the records for a particular category
>> >> > > > of
>> >> > > > racer and
>> >> > > > only select those records to print on the report based on the
>> >> > > > following
>> >> > > > criteria?
>> >> > > >
>> >> > > > If there are between 1-5 racers, I'd select one record to print
>> >> > > > (the record
>> >> > > > with the lowest FinishTime))
>> >> > > > If there are between 6-10 racers, I'd select 2 records to print
>> >> > > > (the records
>> >> > > > with the lowest and second lowest FinishTime)
>> >> > > >
>> >> > > > I am not at all familiar with Visual Basic so am hoping someone
>> >> > > > can
>> >> > > > offer a
>> >> > > > suggestion on how to do this using the Access queries and
>> >> > > > macros.
>> >> > > >
>> >> > > > Thanks
>> >> > >
>> >>
>>
>>
>>

Actually, if that is division as I understand it is supposed to be, the
slash goes the other way (/). Oops.

"BruceM" <bamoob_at_yawhodotcalm.not> wrote in message
news:e%2397$(E-Mail Removed)...
> Why is sorting/grouping not an option? Grouping by category, while
> forcing a new page in the group header and footer, will give you a page
> (or more, if necessary) for each category.
>
> Ken's code and method, as I understand it, creates a text box
> (txtTotalCount) in the header that shows the total number of records, and
> a text box (txtCount) in the detail section that keeps a running sum of
> the number of records. Then this code appears in the Format event for the
> Detail section:
>
> Dim n As Integer
>
> n = Me.txtTotalCount
> Cancel = Me.txtCount > ((n - 1) \ 5) + 1
>
> If the total number of records is 11, plug that into the formula instead
> of "n":
>
> ((11-1) \ 5) + 1
>
> (10 \ 5) + 1
>
> 2 + 1
>
> 3
>
> When Me.txtCount (the running sum text box) shows a value greater than 3,
> the Format event cancels, which is to say it stops, and the report shows
> only the number of records specified by the formula. Perhaps you will
> need to tweak the formula. For instance, maybe >= rather than >, but that
> should be an easy enough adjustment if the rest of it works. The SQL Ken
> showed is to handle ties in case more than one person has the same time
> for first or second place. You would use that as the Record Source for
> the report.
>
> I see no real alternative to using the report's sorting and grouping. It
> is possible to group in a query, and to select some number of top values.
> It may even be possible to adjust the number of top values depending on
> the number of entrants, but such a query structure is beyond what I can
> illustrate.
>
>
> "pauld" <(E-Mail Removed)> wrote in message
> news:C8EC9338-5D46-44E9-AD75-(E-Mail Removed)...
>> There are 50 different categories in the race.
>>
>> I need to print one page per category (long story) so sorting/grouping
>> isn't
>> really an option.
>>
>> Each page has at least one winner up to a maximum of 6 winners. The
>> number
>> of entries drives the number of winners who will get a trophy (and
>> therefore
>> have their names printed on the report).
>>
>> I really don't understand the suggested code and how it is supposed to
>> work.
>> I know this forum isn't a school, but I'd sure like to know what I'm
>> doing.
>> Any chance you could tell me what the code is doing step by step?
>>
>> Thanks.
>>
>>
>>
>> "BruceM" wrote:
>>
>>> Did you try the suggested method? I may not be reading the code
>>> correctly,
>>> but I think the Cancel statement stops the printing when the requisite
>>> number of names have been printed. It don't think it stops after a
>>> maximum
>>> of 6 trophies, but I believe that was an added detail in your most
>>> recent
>>> posting.
>>>
>>> As for categories, can you use the report's Sorting and Grouping to
>>> group by
>>> category? If so, use the group header and footer instead of the report
>>> header and footer, and set the Running Sum to Over Group instead of Over
>>> All.
>>>
>>> "pauld" <(E-Mail Removed)> wrote in message
>>> news:6D75D2CD-976F-4E01-8B52-(E-Mail Removed)...
>>> > Thanks.
>>> >
>>> > I don't think I'm explaining myself correctly...so at the risk of
>>> > writing
>>> > a
>>> > book, let me expand a bit...
>>> >
>>> > We have criteria that says if 1-5 people enter the race, we will hand
>>> > out
>>> > one trophy; if 6-10 people enter the race, we will hand out 2
>>> > trophies,
>>> > etc...all the way up to if more than 25 people enter the race, we will
>>> > hand
>>> > out 6 trophies.
>>> >
>>> > There are many different categories wihin the race so I print a report
>>> > for
>>> > each category showing the winner(s) of that category. I sort based on
>>> > Finish
>>> > Time (ascending) so all of the finishers are listed in the correct
>>> > order.
>>> >
>>> > The problem is I currently have to print all of entrants within a
>>> > specific
>>> > category on each report and manually mark-out the entries that are not
>>> > getting trophies.
>>> >
>>> > What I'd like to be able to do is have the number of entries control
>>> > how
>>> > many records I actually print...so that I don't have any manual
>>> > mark-outs
>>> > on
>>> > the report.
>>> >
>>> > I'd like Access to count the entries (assume it finds 8 entries which
>>> > means
>>> > we will award 2 trophies) and then print only 2 records on the report.
>>> >
>>> > It's the issue of counting the records for the category and having it
>>> > drive
>>> > the number of records to print that is my question.
>>> >
>>> > Hope this helps
>>> >
>>> >
>>> >
>>> > "Ken Sheridan" wrote:
>>> >
>>> >> Firstly don't bother sorting the query unless you are also using it
>>> >> elsewhere, e.g. as the RecordSource for a form; otherwise use the
>>> >> report's
>>> >> internal sorting and grouping mechanism to sort it. Do so even if
>>> >> you do
>>> >> sort the query. Then in the report do the following:
>>> >>
>>> >> 1. Add an unbound text box txtTotalCount to the report header
>>> >> section,
>>> >> with
>>> >> a ControlSource property of =Count(*). Set its Visible property to
>>> >> False
>>> >> (No).
>>> >>
>>> >> 2. Add an unbound text box txtCount to the detail section, with a
>>> >> ControlSource property of =1. Set its RunningSum property to 'Over
>>> >> All'
>>> >> and
>>> >> its Visible property to False (No).
>>> >>
>>> >> 3. In the detail section's Format event procedure put:
>>> >>
>>> >> Dim n As Integer
>>> >>
>>> >> n = Me.txtTotalCount
>>> >> Cancel = Me.txtCount > ((n - 1) \ 5) + 1
>>> >>
>>> >> Note that a simple query, and consequently a report using the above
>>> >> will
>>> >> not handle ties for the final relevant place, however, as only one of
>>> >> the
>>> >> runners with equal times will appear in the report, so if there are 8
>>> >> runners
>>> >> and one is the overall winner but two tie with equal second place
>>> >> times,
>>> >> one
>>> >> of the latter would unjustifiably be excluded from the report. To
>>> >> cater
>>> >> for
>>> >> that you'd need to use a query which ranks the places and gives equal
>>> >> ranks
>>> >> to ties, e.g.
>>> >>
>>> >> SELECT R1.*,
>>> >> (SELECT COUNT(*)
>>> >> FROM RaceResults AS R2
>>> >> WHERE R2.Race = R1.Race
>>> >> AND R2.Time < R1.Time)+1 AS Position
>>> >> FROM RaceResults AS R1
>>> >> WHERE Race = [Enter Race:];
>>> >>
>>> >> You'd then sort the report by Position. You'd then have a text box
>>> >> bound
>>> >> to
>>> >> the position column in the detail section rather than the unbound
>>> >> txtCount
>>> >> control, and the code in the detail sections' Format event would be:
>>> >>
>>> >> Dim n As Integer
>>> >>
>>> >> n = Me.txtTotalCount
>>> >> Cancel = Me.Position > ((n - 1) \ 5) + 1
>>> >>
>>> >> You refer to macros, and say that you are not be familiar with
>>> >> entering
>>> >> VBA
>>> >> code in the event procedures of reports or forms. This is how its
>>> >> done:
>>> >>
>>> >> Select the section by clicking on its title bar in report design view
>>> >> and
>>> >> open its properties sheet if its not already open. Then select the
>>> >> Format
>>> >> event property in the properties sheet. Click on the 'build' button;
>>> >> that's
>>> >> the one on the right with 3 dots. Select 'Code Builder' in the
>>> >> dialogue,
>>> >> and
>>> >> click OK. The VBA window will open at the event procedure with the
>>> >> first
>>> >> and
>>> >> last lines already in place. Enter the lines of code between these
>>> >> two
>>> >> existing lines.
>>> >>
>>> >> Ken Sheridan
>>> >> Stafford, England
>>> >>
>>> >> "pauld" wrote:
>>> >>
>>> >> > Thanks for your response,
>>> >> >
>>> >> > I use a simple query sorting on finish time from lowest to highest
>>> >> > to
>>> >> > determine who is first, second, third, etc. That works just fine.
>>> >> >
>>> >> > What I'm trying to do is limit the number of winners I show on the
>>> >> > report.
>>> >> > The number of winners is based on the number of entries. If the
>>> >> > number
>>> >> > of
>>> >> > entries is 1-5, I award a trophy to one winnner. If the number of
>>> >> > entries is
>>> >> > 6-10, I award 2 trophies, etc
>>> >> >
>>> >> > How do I limit the number of records I show based on the count of
>>> >> > the
>>> >> > entries for that race?
>>> >> >
>>> >> > "John Spencer MVP" wrote:
>>> >> >
>>> >> > > You will need a ranking query to rank the racers in each category
>>> >> > >
>>> >> > > This might work.
>>> >> > >
>>> >> > > SELECT RA.RunnerID, Count(RB.FinishTime) as Rank,
>>> >> > > FROM Races as RA INNER JOIN Races as RB
>>> >> > > ON RA.Category = RB.Category
>>> >> > > AND RA.FinishTime > RB.FinishTime
>>> >> > > GROUP BY RA.RunnerID
>>> >> > > HAVING Count(RB.FinishTime) <=
>>> >> > > (SELECT Count(*) FROM Races as RC WHERE RC.Category =
>>> >> > > Ra.Category)\5
>>> >> > >
>>> >> > > John Spencer
>>> >> > > Access MVP 2002-2005, 2007-2009
>>> >> > > The Hilltop Institute
>>> >> > > University of Maryland Baltimore County
>>> >> > >
>>> >> > > pauld wrote:
>>> >> > > > I have a report that shows the winners of a race.
>>> >> > > >
>>> >> > > > The number of winner medals is based on the number of people
>>> >> > > > who
>>> >> > > > enter in
>>> >> > > > their specific category. There are many categories.
>>> >> > > >
>>> >> > > > Is there any way to count the records for a particular category
>>> >> > > > of
>>> >> > > > racer and
>>> >> > > > only select those records to print on the report based on the
>>> >> > > > following
>>> >> > > > criteria?
>>> >> > > >
>>> >> > > > If there are between 1-5 racers, I'd select one record to print
>>> >> > > > (the record
>>> >> > > > with the lowest FinishTime))
>>> >> > > > If there are between 6-10 racers, I'd select 2 records to print
>>> >> > > > (the records
>>> >> > > > with the lowest and second lowest FinishTime)
>>> >> > > >
>>> >> > > > I am not at all familiar with Visual Basic so am hoping someone
>>> >> > > > can
>>> >> > > > offer a
>>> >> > > > suggestion on how to do this using the Access queries and
>>> >> > > > macros.
>>> >> > > >
>>> >> > > > Thanks
>>> >> > >
>>> >>
>>>
>>>
>>>
>
>

The backslash is right; it’s the integer division operator.

As regards the categories, grouping by category is the obvious solution,
forcing a new page after each group. The running sum would then be set to
'over group'. As the number of awards is now a ratio of the total number per
group, then the txtTotalCount control should now go in the group header not
the form header. I think that should give the desired result.

If a ranking query is used as the report's RecordSource, however, as should
really be the case, then the subquery would need to be correlated with the
outer query on the category column; I called it Race in my example. The
WHERE clause with its [Enter Race:] parameter would no longer be required as
the query would now be returning all races (categories). One would normally
expect that the table would cover multiple 'meetings' with the same
categories at different meetings, however, in which case it would be
necessary to restrict the query on the 'meeting' column and also include this
in the correlation of the subquery with the outer query, which would
therefore be something like this:

SELECT R1.*,
(SELECT COUNT(*)
FROM RaceResults AS R2
WHERE R2.Meeting = R1.Meeting
AND R2.Category = R1.Category
AND R2.Time < R1.Time)+1 AS Position
FROM RaceResults AS R1
WHERE Meeting = [Enter Meeting:];

Ken Sheridan
Stafford, England

"BruceM" wrote:

> Actually, if that is division as I understand it is supposed to be, the
> slash goes the other way (/). Oops.
>
> "BruceM" <bamoob_at_yawhodotcalm.not> wrote in message
> news:e%2397$(E-Mail Removed)...
> > Why is sorting/grouping not an option? Grouping by category, while
> > forcing a new page in the group header and footer, will give you a page
> > (or more, if necessary) for each category.
> >
> > Ken's code and method, as I understand it, creates a text box
> > (txtTotalCount) in the header that shows the total number of records, and
> > a text box (txtCount) in the detail section that keeps a running sum of
> > the number of records. Then this code appears in the Format event for the
> > Detail section:
> >
> > Dim n As Integer
> >
> > n = Me.txtTotalCount
> > Cancel = Me.txtCount > ((n - 1) \ 5) + 1
> >
> > If the total number of records is 11, plug that into the formula instead
> > of "n":
> >
> > ((11-1) \ 5) + 1
> >
> > (10 \ 5) + 1
> >
> > 2 + 1
> >
> > 3
> >
> > When Me.txtCount (the running sum text box) shows a value greater than 3,
> > the Format event cancels, which is to say it stops, and the report shows
> > only the number of records specified by the formula. Perhaps you will
> > need to tweak the formula. For instance, maybe >= rather than >, but that
> > should be an easy enough adjustment if the rest of it works. The SQL Ken
> > showed is to handle ties in case more than one person has the same time
> > for first or second place. You would use that as the Record Source for
> > the report.
> >
> > I see no real alternative to using the report's sorting and grouping. It
> > is possible to group in a query, and to select some number of top values.
> > It may even be possible to adjust the number of top values depending on
> > the number of entrants, but such a query structure is beyond what I can
> > illustrate.
> >
> >
> > "pauld" <(E-Mail Removed)> wrote in message
> > news:C8EC9338-5D46-44E9-AD75-(E-Mail Removed)...
> >> There are 50 different categories in the race.
> >>
> >> I need to print one page per category (long story) so sorting/grouping
> >> isn't
> >> really an option.
> >>
> >> Each page has at least one winner up to a maximum of 6 winners. The
> >> number
> >> of entries drives the number of winners who will get a trophy (and
> >> therefore
> >> have their names printed on the report).
> >>
> >> I really don't understand the suggested code and how it is supposed to
> >> work.
> >> I know this forum isn't a school, but I'd sure like to know what I'm
> >> doing.
> >> Any chance you could tell me what the code is doing step by step?
> >>
> >> Thanks.
> >>
> >>
> >>
> >> "BruceM" wrote:
> >>
> >>> Did you try the suggested method? I may not be reading the code
> >>> correctly,
> >>> but I think the Cancel statement stops the printing when the requisite
> >>> number of names have been printed. It don't think it stops after a
> >>> maximum
> >>> of 6 trophies, but I believe that was an added detail in your most
> >>> recent
> >>> posting.
> >>>
> >>> As for categories, can you use the report's Sorting and Grouping to
> >>> group by
> >>> category? If so, use the group header and footer instead of the report
> >>> header and footer, and set the Running Sum to Over Group instead of Over
> >>> All.
> >>>
> >>> "pauld" <(E-Mail Removed)> wrote in message
> >>> news:6D75D2CD-976F-4E01-8B52-(E-Mail Removed)...
> >>> > Thanks.
> >>> >
> >>> > I don't think I'm explaining myself correctly...so at the risk of
> >>> > writing
> >>> > a
> >>> > book, let me expand a bit...
> >>> >
> >>> > We have criteria that says if 1-5 people enter the race, we will hand
> >>> > out
> >>> > one trophy; if 6-10 people enter the race, we will hand out 2
> >>> > trophies,
> >>> > etc...all the way up to if more than 25 people enter the race, we will
> >>> > hand
> >>> > out 6 trophies.
> >>> >
> >>> > There are many different categories wihin the race so I print a report
> >>> > for
> >>> > each category showing the winner(s) of that category. I sort based on
> >>> > Finish
> >>> > Time (ascending) so all of the finishers are listed in the correct
> >>> > order.
> >>> >
> >>> > The problem is I currently have to print all of entrants within a
> >>> > specific
> >>> > category on each report and manually mark-out the entries that are not
> >>> > getting trophies.
> >>> >
> >>> > What I'd like to be able to do is have the number of entries control
> >>> > how
> >>> > many records I actually print...so that I don't have any manual
> >>> > mark-outs
> >>> > on
> >>> > the report.
> >>> >
> >>> > I'd like Access to count the entries (assume it finds 8 entries which
> >>> > means
> >>> > we will award 2 trophies) and then print only 2 records on the report.
> >>> >
> >>> > It's the issue of counting the records for the category and having it
> >>> > drive
> >>> > the number of records to print that is my question.
> >>> >
> >>> > Hope this helps
> >>> >
> >>> >
> >>> >
> >>> > "Ken Sheridan" wrote:
> >>> >
> >>> >> Firstly don't bother sorting the query unless you are also using it
> >>> >> elsewhere, e.g. as the RecordSource for a form; otherwise use the
> >>> >> report's
> >>> >> internal sorting and grouping mechanism to sort it. Do so even if
> >>> >> you do
> >>> >> sort the query. Then in the report do the following:
> >>> >>
> >>> >> 1. Add an unbound text box txtTotalCount to the report header
> >>> >> section,
> >>> >> with
> >>> >> a ControlSource property of =Count(*). Set its Visible property to
> >>> >> False
> >>> >> (No).
> >>> >>
> >>> >> 2. Add an unbound text box txtCount to the detail section, with a
> >>> >> ControlSource property of =1. Set its RunningSum property to 'Over
> >>> >> All'
> >>> >> and
> >>> >> its Visible property to False (No).
> >>> >>
> >>> >> 3. In the detail section's Format event procedure put:
> >>> >>
> >>> >> Dim n As Integer
> >>> >>
> >>> >> n = Me.txtTotalCount
> >>> >> Cancel = Me.txtCount > ((n - 1) \ 5) + 1
> >>> >>
> >>> >> Note that a simple query, and consequently a report using the above
> >>> >> will
> >>> >> not handle ties for the final relevant place, however, as only one of
> >>> >> the
> >>> >> runners with equal times will appear in the report, so if there are 8
> >>> >> runners
> >>> >> and one is the overall winner but two tie with equal second place
> >>> >> times,
> >>> >> one
> >>> >> of the latter would unjustifiably be excluded from the report. To
> >>> >> cater
> >>> >> for
> >>> >> that you'd need to use a query which ranks the places and gives equal
> >>> >> ranks
> >>> >> to ties, e.g.
> >>> >>
> >>> >> SELECT R1.*,
> >>> >> (SELECT COUNT(*)
> >>> >> FROM RaceResults AS R2
> >>> >> WHERE R2.Race = R1.Race
> >>> >> AND R2.Time < R1.Time)+1 AS Position
> >>> >> FROM RaceResults AS R1
> >>> >> WHERE Race = [Enter Race:];
> >>> >>
> >>> >> You'd then sort the report by Position. You'd then have a text box
> >>> >> bound
> >>> >> to
> >>> >> the position column in the detail section rather than the unbound
> >>> >> txtCount
> >>> >> control, and the code in the detail sections' Format event would be:
> >>> >>
> >>> >> Dim n As Integer
> >>> >>
> >>> >> n = Me.txtTotalCount
> >>> >> Cancel = Me.Position > ((n - 1) \ 5) + 1
> >>> >>
> >>> >> You refer to macros, and say that you are not be familiar with
> >>> >> entering
> >>> >> VBA
> >>> >> code in the event procedures of reports or forms. This is how its
> >>> >> done:
> >>> >>
> >>> >> Select the section by clicking on its title bar in report design view
> >>> >> and
> >>> >> open its properties sheet if its not already open. Then select the
> >>> >> Format
> >>> >> event property in the properties sheet. Click on the 'build' button;
> >>> >> that's
> >>> >> the one on the right with 3 dots. Select 'Code Builder' in the
> >>> >> dialogue,
> >>> >> and
> >>> >> click OK. The VBA window will open at the event procedure with the
> >>> >> first
> >>> >> and
> >>> >> last lines already in place. Enter the lines of code between these
> >>> >> two
> >>> >> existing lines.
> >>> >>
> >>> >> Ken Sheridan
> >>> >> Stafford, England
> >>> >>
> >>> >> "pauld" wrote:
> >>> >>
> >>> >> > Thanks for your response,
> >>> >> >
> >>> >> > I use a simple query sorting on finish time from lowest to highest
> >>> >> > to
> >>> >> > determine who is first, second, third, etc. That works just fine.
> >>> >> >
> >>> >> > What I'm trying to do is limit the number of winners I show on the
> >>> >> > report.
> >>> >> > The number of winners is based on the number of entries. If the
> >>> >> > number
> >>> >> > of
> >>> >> > entries is 1-5, I award a trophy to one winnner. If the number of
> >>> >> > entries is
> >>> >> > 6-10, I award 2 trophies, etc
> >>> >> >
> >>> >> > How do I limit the number of records I show based on the count of
> >>> >> > the
> >>> >> > entries for that race?
> >>> >> >
> >>> >> > "John Spencer MVP" wrote:
> >>> >> >
> >>> >> > > You will need a ranking query to rank the racers in each category
> >>> >> > >
> >>> >> > > This might work.
> >>> >> > >
> >>> >> > > SELECT RA.RunnerID, Count(RB.FinishTime) as Rank,
> >>> >> > > FROM Races as RA INNER JOIN Races as RB
> >>> >> > > ON RA.Category = RB.Category
> >>> >> > > AND RA.FinishTime > RB.FinishTime
> >>> >> > > GROUP BY RA.RunnerID
> >>> >> > > HAVING Count(RB.FinishTime) <=
> >>> >> > > (SELECT Count(*) FROM Races as RC WHERE RC.Category =
> >>> >> > > Ra.Category)\5
> >>> >> > >
> >>> >> > > John Spencer
> >>> >> > > Access MVP 2002-2005, 2007-2009
> >>> >> > > The Hilltop Institute
> >>> >> > > University of Maryland Baltimore County
> >>> >> > >
> >>> >> > > pauld wrote:
> >>> >> > > > I have a report that shows the winners of a race.
> >>> >> > > >
> >>> >> > > > The number of winner medals is based on the number of people
> >>> >> > > > who
> >>> >> > > > enter in
> >>> >> > > > their specific category. There are many categories.
> >>> >> > > >
> >>> >> > > > Is there any way to count the records for a particular category
> >>> >> > > > of
> >>> >> > > > racer and
> >>> >> > > > only select those records to print on the report based on the
> >>> >> > > > following
> >>> >> > > > criteria?
> >>> >> > > >
> >>> >> > > > If there are between 1-5 racers, I'd select one record to print
> >>> >> > > > (the record
> >>> >> > > > with the lowest FinishTime))
> >>> >> > > > If there are between 6-10 racers, I'd select 2 records to print
> >>> >> > > > (the records
> >>> >> > > > with the lowest and second lowest FinishTime)
> >>> >> > > >
> >>> >> > > > I am not at all familiar with Visual Basic so am hoping someone
> >>> >> > > > can
> >>> >> > > > offer a
> >>> >> > > > suggestion on how to do this using the Access queries and
> >>> >> > > > macros.
> >>> >> > > >
> >>> >> > > > Thanks
> >>> >> > >
> >>> >>
> >>>
> >>>