trying to aggregate by two variables

[charlesoutcalt]

Hello All. I have a question that might be simple for some of you, but it's been stumping me for weeks.

I have a database of financial aid awards. Each record represents one award. Records contain student id numbers, and award types (federal loan, scholarship, etc.). There are about 40,000 records, and, within those, about 4,000 students and 14 types of awards.

Thanks to some really great help from people on this forum, I've gotten as far as I have, but I need advice on how to get to the next step.

I would like to be able to create a field for each type of award, and then total all the awards of that type for each student. My ultimate goal is to create a table in which students are arranged by ID, with their totals in each financial aid type. I would like these totals to be individual fields, so that I can search, for example, for all students with loans more than $5,000, or scholarships less than $1,000 (etc.).

Thank you!

[PeterHW]

Hi,

seems you have a typical many-to-many relationship: I assume that one student can have multiple awards, and that one award can be awarded to multiple students. The best way to handle this would be to make a separate table for students and a separate table for awards. Then, you would make what is called a join table (which 'joins' the two tables), where each record represents the pairing of one student to one award.

With a design like this, getting the result you are looking for would be real easy, and I would urge you to try and implement that design as quickly as possible. I have the feeling you will want to add more features to your database in the future, and in such a case it would be wise to invest in a good design now, so future additions will go much and much smoother.

Creating these tables will not be difficult, and you can import the data of your current table to the new tables. Creating the join table will be somewhat more involved, but in all likelihood much of it can be scripted using the data from your current table. Come to think of it, your current table IS a join table, all you need to do is create a Students table and an Awards table and you're there. Just to clarify, you will have the following setup :

The table Students is linked to JoinTable based on StudentID (check 'allow creation of records')

JoinTable is linked to Awards based on AwardsID. You can fill the student table by importing from your current table (just import the student related fields) and then removing the duplicates. You should wind up with only one record for one unique student. The same procedure for the Awards table. After finishing, you can see which awards a students has from the Student table (using a portal or different methods) and from the Awards table you can see which students received a particular award. From the join table, you can run all sorts of reports.

The result you are looking for can be obtained with your current setup, but I'd really advise against it, because it will only give you a quick fix, without solving the underlying problems of your design.

Let me know if you need any further help with setting up the tables.

Regards,

Peter

[charlesoutcalt]

Peter, this is tremendously helpful. As you can tell, I'm a FileMaker beginner, and these issues are new to me.

Can I ask a couple more questions to explore this further?

I tried to keep my original post short and to the point, but there are a few more complications.

The biggest complication is that I have one award file for each academic year. There are 10 of them in all (1995 through 2005). I figured that whatever solution I reached for one year could be adapted for all years, but your response made me realize that this is more about joining tables than creating fields.

The second complication is that each award category (loan, grant, etc.) contains many different award type (federal loan, private loan, etc., are all types in the loan category). For two years of the award files, I have manually matched award types to award categories, but that is extremely tedious (at least half a day per file).

That said, I have already created a file of unique student IDs. This table contains one record for each student ID (spanning all the years). In addition, I have another table for the 200 or so unique award types, and another file for the 14 unique award categories.

From what I understand, I need to do two things:

a) Create the join tables you mentioned. I am not at all familiar with this concept, and would welcome any advice you have.

: Find a way to categorize each of my award types into categories (for the years I have not yet done manually).

Any advice on how to accomplish these two tasks?

[charlesoutcalt]

Update:

I have spent a couple hours strugglin with this problem. I have added a portal to a layout based on my student ID file. That portal shows each award category students have received, so that is great.

There are a couple problems, though: I can't show the amount of the award, because I can't figure out how to create a total for that award category. Also, I don't know how to categorize the award types into award categories without manually search for instances of a particular type, then assigning that type to a category.

I know this could be easier, but I'm just not seeing it yet. I have a hunch the solution will come through those join tables.

[PeterHW]

Hi Charles,

a join table can be a little confusing when you are just starting out. To simplify things I've made a small demo file which shows what I mean. Try looking at the structure. I've left field comments and also comments in the layouts.

here are a couple problems, though: I can't show the amount of the award, because I can't figure out how to create a total for that award category

There are several approaches to this, two of which are shown in my sample file : you can either use a filtered relationship (where only the records from the join file are returned which match StudentID AND AwardCategory, or you can use a report approach using summaries on your layout (which is based on the join table).

Also, I don't know how to categorize the award types into award categories without manually search for instances of a particular type, then assigning that type to a category.

I've solved this using a simple calculation, although as mentioned in the comments, if you have a lot of categories, I would advise you to put them in a separate table.

Just a quick tip : a very fast way of replacing field contents is by using the (very appropriately named) "Replace field contents" command from the Records menu : this will change a fields contents for all records in the currently found set.

So first make sure you only have the records in your found set that you wish to change. So let's say your AwardType field contains "personal loan", "federal loan" "some other loan" "totally different kind of loan" etc., you simply perform a search for "loan". Now, put your cursor in the AwardCategory field and choose "Replace field contents". This will bring up a dialog, where you have three choices : you can replace all records with the value of the current record (so if the records you are currently in already has the right value you can select this). Or you can use a serial number (not much use here). Or thirdly, you can specify a calculation. In case your current record does not contain the right value, you choose calculation. In the calculation box, you type "loan" (with the quotes !). Then press OK and all records will have "loan" in the category field. Note that this is not reversible (although you can of course again perform the same procedure with another, or blank value).

Anyway, I hope this helps you out, let me know if you need additional help.

Regards,

Peter

StudentsAndAwards.zip

[charlesoutcalt]

Peter, this information, and the sample file, are EXTREMELY helpful. I will take what you said and study it so I can make some progress on this project.

Thanks again. I owe you one--or two, or six!

[PeterHW]

Hi Charles, glad it helped you on your way. I'll definitely take a raincheck on those 6 ! :

Good luck and let me know if you run into any more problems.

Best regards,

Peter