9 Replies Latest reply on Jan 9, 2013 12:38 PM by philmodjunk

    Relationship and referencing (newb)



      Relationship and referencing (newb)



           Apologies but I really didn't know how to search for this.  Thanks in advance.

           Let's say, I have a database that has 3 tables:  Students, Scores, Averages.

           All are linked with a Student ID that is a unique value.

           I do not know how to make a relationship to show 'averages' show the averages of the scores.  The problem is the relationship.  If I type in the student ID in the averages table, it will show the appropriate average.  How do I relate the tables to not have to copy the Student IDs into the Averages table?  

           I could easy put the 'Averages' fields in the Student table but I would like to separate this into it's own table.

           Again, thanks.

        • 1. Re: Relationship and referencing (newb)

               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.

          • 2. Re: Relationship and referencing (newb)


                      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'.


            • 3. Re: Relationship and referencing (newb)

                   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 absent present "reward" them for being absent by computing a higher "average" than a student with perfect attendance?

              • 4. Re: Relationship and referencing (newb)

                     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.  

                • 5. Re: Relationship and referencing (newb)

                       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?

                  • 6. Re: Relationship and referencing (newb)

                         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 wink

                         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.

                    • 7. Re: Relationship and referencing (newb)

                           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.

                      • 8. Re: Relationship and referencing (newb)

                             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.

                        • 9. Re: Relationship and referencing (newb)

                               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.