Help sorting report by calculated field in a group footer on repor

[Guest]

Background Info:
I have two tables: one for student name, one for scholarship info.

I have a query: that pulls in student name, scholarship name, scholarship
amount(s) for all students. Students may receive more than one scholarship.

I have a report: that uses the query above as the data source. The report
is currently grouped by student name. Under the student name, the scholarship
data is sorted by scholarship name and scholarship amount are shown. In the
group footer I am calculating the sum([scholarship amount]) and calculating
the count([scholarshipname]) per student. I need to know how much $ each
student gets and I know how many scholarships they've received.

Problem:
I would like to sort/filter this report by the total scholarship amount per
student ** but not break the grouping by student that gives me the total **

And, I need to create another report that sorts/filter by the "count" of
awards per student ** but not break the grouping by student that gives me the
count.**

I figure there has to be a solution somewhere. I've looked in help and
online and have not been able to find any help!

I found one help link that mentioned adding the calculated field to the the
query . The example showed "quanity" and a "price" where they found the
extended price (=[quantity]*[price]). I understand this expression. It is
basic but it is using two different fields. I don't see how I can sum or
count one column in a query. If I "count" my scholarship awards or "sum" my
scholarship amounts I get an answer of "1" per each row in the query... This
doesnt do me any good because it does not take the grouping by student into
consideration....

Idealling I could sort by a group footer in the report but I don't see where
that is an option.

Any ideas?

I'm only partially knowledgable when it comes to Access so be gentle!

 

[Duane Hookom]

Create a totals query that Groups By student and Counts and Sums scholarship
info. Add this totals query to your report's record source query. Join the
StudentID (or similar) fields. Add the Count and the Sum fields to the query
grid so that you can use them to sort by.

 

[Guest]

Your answer worked perfectly.

I've been in a number of Access classes, most of which are pretty basic,
even though they call them "advanced" and no one has ever shown me a total
query, let only how it could be beneficial to duplicate some of the query
data in order to get the field that I needed!

It was so easy after I built the total query and linked it to my first query
that I was able to do three more. I had to watch for duplicate info. It
seemed to happen when I pulled extra fields into the new total query. It was
a quick fix of removing the extra fields. Thanks for your time.... I'd kiss
you if you were closer!!!!!!!!!!!!!!!!