Can you describe how, "on paper" your averages calculations will work?
It's very possible that you can remove the Averages table, using an "average" summary field defined in Scores instead.
Thanks for the reply.
A little simplified, hopefully not over simplifed: My 'Scores' table is comprised of the following fields: Date, StudentID, HomeWork (1 or blank value) and Participation (1 or blank). I would like to get the sum of home work and devide by a count of days they were present, for instance. I have the formulas in place but the 'Averages' table only will populate if I manually enter the 'StudentID'.
Does a 1 in the participation table indicate that the student was present? If not, how do you record the number of days that a student was present?
if so, a Summary field in Scores can compute the total score, a second summary field can sum up the values of the Participation field and this calculation can compute the "average" for each student over all their scores:
GetSummary ( sTotalScore ; StudentID ) / getSummary ( sTotalParticipation ; StudentID )
Such a calculation would be used for a summary report based on the scores table such that you can list each student, their individual scores and subtotals and "averages" for each.
As you have indicated, it's also possible to set up the same calculation using sum functions from the Students table.
I see no advantage and a disadvantage in the form of added complexities to your design in having a separate Averages table--given what little I know about your database, but there are ways to use a script trigger to automatically create a related record in Averages, each time you add a new record to Students and then you could use your exisitng set up as well.
PS. Doesn't dividing by the number days that a student is
absentpresent "reward" them for being absent by computing a higher "average" than a student with perfect attendance?
Thanks for the information. I apologize for not being clear. When a student is absent, there is no date entry for their student ID that day. In averages, I have a count of dates for that student. So, if they were present there would be a date with their ID for that day. If they turned in the assigned home work and participated, there would be a "1" value for these. So, Averages::Homework = count(Scores::Date)/sum(Scores::Homework)
Is there a way to do it with a separate table without creating the script? The Scores and Students tables have many fields and I was looking for a way to have the data in another table. I wouldn't want to increase the size of the database to do this though.
Forgot to mention, I will also be making the database to be searched via date range. Wouldn't doing the summary field prevent me from doing this?
You have to link the averages table to the students table by student ID if you insist on putting averages in a separate table. This won't happen by itself and we have yet to create a mind reading computer
Thus, you have to have some way to create a record in averages for every record in students, with the correct value in the studentID field.
If you add in a second occurrence of Averages, you can link it directly to students like this:
Students::StudentID = studentsAVERAGES::StudentID
and you can enable "allow creation of records via this relationship".
Then, on your students layout, you can use the OnCommitRecords script trigger to perform a one line script step:
Set Field [studentsAVERAGES::StudentID ; Students::StudentID]
The first time commit record occurs on a record in students on your layout, a new matching record in Averages will be automatically created. Any subsequent times that commit record occurs, no change to the averages table will take place.
Unfortunately, the database is more complicated than I let on. I think this may be the best route for me. I do not want to have 20-30 records in a table. Where is "oncommitrecords"? I cannot find it. I am using FM Pro 12, if that helps.
Sorry. I found it. Thanks again.
I'm curious, how else would someone make a table less complicated? I want to make sure there are not 20+ fields per table.
20 plus fields in a table doesn't sound like all that many fields for one table at all. 100+ is not unheard of, though if you get that many, you do need to stop and review whether you need to split things up to use a related table or not.