COUNT RECORDS TO PRINT ON REPORT

[pauld]

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

 

[John Spencer MVP]

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]

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?

 

[Ken Sheridan]

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]

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

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

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?

 

[BruceM]

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.

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

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

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?

:

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

 

[pauld]

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.

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.

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

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

:

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?

:

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

 

[BruceM]

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.

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.

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.

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



:

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

:

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?

:

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

 

[BruceM]

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

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.

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.

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.

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



:

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

:

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?

:

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

 

[Ken Sheridan]

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

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

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.

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.



:

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.

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



:

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

:

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?

:

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

 

[BruceM]

I thought I had made a typo. I didn't look back at the original expression.
It sounds familiar, now that I think about it, but I had forgotten. I see
from some testing that it is the same as Int(a/b), but that an integer
variable in VBA rounds up rather than returning the integer portion of a
number. Anyhow, now that I have tested I may remember it.

I think I can follow what is going on in the most recent SQL you posted
here, but I doubt I could have come up with it very readily. Thanks for the
additional explanations.

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

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

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.


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.



:

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.

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



:

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

:

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?

:

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

 

[pauld]

Am I trying to bite off too much???

There are 50 race categories (classes). Each report that I print today has 4
rows of header info specific to that class. In the Detail section of the
report, I show the entries for that class sorted ascending by Finish Time.

I thought it would be a simple (sort of) process to count the records for
that class and then print the appropriate number based on an IF/THEN type of
command.

Like IF the number of entries (records) for Class "A" is between 1 and 5,
THEN print one record.

Or IF the number of entries (records) for Class "A" is between 6 and 10,
THEN print 2 records.

It seems like the records would have to be sorted ahead of time (by a
query?) to be sure the best finish time is the first record to print...and
the second best is the second record, etc...

Am I trying to do something too difficult?

I thought I had made a typo. I didn't look back at the original expression.
It sounds familiar, now that I think about it, but I had forgotten. I see
from some testing that it is the same as Int(a/b), but that an integer
variable in VBA rounds up rather than returning the integer portion of a
number. Anyhow, now that I have tested I may remember it.

I think I can follow what is going on in the most recent SQL you posted
here, but I doubt I could have come up with it very readily. Thanks for the
additional explanations.

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

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
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.


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.



:

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.

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



:

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

 

[BruceM]

I have to admit I do not understand all the details of what Ken wrote, but I
still think grouping by Class could work. You say there are 50 race
categories, and I still don't follow whether there can be more than one race
per category over time, but in any case I don't understand the basis for
your statement that grouping is not an option. Have you tried it and found
it to be inadequate for your needs?

Am I trying to bite off too much???

There are 50 race categories (classes). Each report that I print today has
4
rows of header info specific to that class. In the Detail section of the
report, I show the entries for that class sorted ascending by Finish Time.

I thought it would be a simple (sort of) process to count the records for
that class and then print the appropriate number based on an IF/THEN type
of
command.

Like IF the number of entries (records) for Class "A" is between 1 and 5,
THEN print one record.

Or IF the number of entries (records) for Class "A" is between 6 and 10,
THEN print 2 records.

It seems like the records would have to be sorted ahead of time (by a
query?) to be sure the best finish time is the first record to print...and
the second best is the second record, etc...

Am I trying to do something too difficult?

I thought I had made a typo. I didn't look back at the original
expression.
It sounds familiar, now that I think about it, but I had forgotten. I
see
from some testing that it is the same as Int(a/b), but that an integer
variable in VBA rounds up rather than returning the integer portion of a
number. Anyhow, now that I have tested I may remember it.

I think I can follow what is going on in the most recent SQL you posted
here, but I doubt I could have come up with it very readily. Thanks for
the
additional explanations.

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

:

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
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.


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.



:

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.

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



:

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