How to aggregate by multiple criteria?

[charlesoutcalt]

Hello All. I'm struggling with a question that I think is probably pretty simple to resolve, but I just can't find the solution.

I have a database of about 35,000 records (of student financial aid awards). Each of those records has a student ID number, the dollar amount of the award, and the type of the award (loan, grant, etc.).

I would like to find the total dollar amount of each type of award for each student. In other words, I want to be able to say "Student Number 1323 had a total of $750 in loans, $300 in grants and $1000 in scholarships."

How can I calculate a total according to these two criteria: student ID and award type?

By the way, I posted this to the portal board, because that seemed the most appropriate, but I don't care whether my totals are in a portal or not. However, I WOULD like to be able to store it for presentation, sorting, etc., on other layouts.

Thanks!

Edited May 19, 2006 by Guest

[IdealData]

It's actually a relationships issue, but it's okay here for the moment.

You need to create a relationship which matches fields in the same table. This is a called a self-join, but it's quite sensible in the relationship definitions. You need to use student_id AND award_type as the matching fields TO THEMSELVES.

Once the relationship is established then the calculation you need is

SUM (student_award_relationship::dollar_amount)

But you can use my example attached

Untitled.fp7_3.zip

[charlesoutcalt]

I am very, very grateful for your assistance!

I have followed your suggestions, and now have a useful field that shows the total award amount for each student when I search for a particular financial aid category.

To complete this project, I would like to find a way to display each of the total amounts for each category. Ideally, I would have one record for each student, with a table showing the several financial aid categories, followed by each category's dollar amount. Is this a job for a portal?

Thank you again for your truly invaluable assistance.