Report Aggregate problem

[James Minns]

Hi all,

I probably haven't understood how the aggregate functions are supposed to
work, but I have the following problem:

I have a report on student marks, based on a query, grouped by the students
Class name.

In the header I have:
Class Name

In the details part of the report I have:
Name, Marks, Comments

and in the footer for the class I have:
Worst mark: =Min(Marks)
Best mark: =Max(Marks)
Average mark: =Avg(Marks)

So in the footer for each class I see the lowest, average and highest mark.
All this works.
Now, I want to print the students name next to the Worst mark and Best mark
in the group footer. The Min/Max function finds the correct record but how
can I grab the student's name who got those marks, like this?
Worst mark: 2 James
Best mark: 15 Helen

Thanks for any help!
James

 

[Marshall Barton]

I probably haven't understood how the aggregate functions are supposed to
work, but I have the following problem:

I have a report on student marks, based on a query, grouped by the students
Class name.

In the header I have:
Class Name

In the details part of the report I have:
Name, Marks, Comments

and in the footer for the class I have:
Worst mark: =Min(Marks)
Best mark: =Max(Marks)
Average mark: =Avg(Marks)

So in the footer for each class I see the lowest, average and highest mark.
All this works.
Now, I want to print the students name next to the Worst mark and Best mark
in the group footer. The Min/Max function finds the correct record but how
can I grab the student's name who got those marks, like this?
Worst mark: 2 James
Best mark: 15 Helen

I don't think there's an easy way to do this, especially if
you might have to deal with the possibility of a tie for the
highest or lowest marks.

 

[James Minns]

James Minns wrote: [snip]
I don't think there's an easy way to do this, especially if
you might have to deal with the possibility of a tie for the
highest or lowest marks.

There is no possibility of a tie. But I can't think of a method, easy or
not!
Thanks, James

 

[Marshall Barton]

[snip]

I don't think there's an easy way to do this, especially if
you might have to deal with the possibility of a tie for the
highest or lowest marks.

There is no possibility of a tie. But I can't think of a method, easy or
not!

As long as ties are not possible, let's try this. Use a
DLookup to retrieve the name that has the lowest mark
=DLookup("[Name]", "thetable", "Mark = " & txtMinMark)

 

[James Minns]

James Minns wrote:

[snip]

I don't think there's an easy way to do this, especially if
you might have to deal with the possibility of a tie for the
highest or lowest marks.

There is no possibility of a tie. But I can't think of a method, easy or
not!

As long as ties are not possible, let's try this. Use a
DLookup to retrieve the name that has the lowest mark
=DLookup("[Name]", "thetable", "Mark = " & txtMinMark)

Problem solved! Thankyou