7 Replies Latest reply on Jul 10, 2017 11:57 AM by haider2016

    Relation problem

    haider2016

      Hi there,

       

      I have three tables: student, subjects and grades. Every student has many subjects, and for every subject there is a grade. when I try to export the student name and all his subjects and their grades to an excel file, only student and subjects are exported correctly, the grades seem to be put randomly and also they are not correct.

       

       

      tables:

       

      STUDEND ----------------< Subjects----------------- grades

       

       

      any suggestions ?? Please

       

       

      Thanks in advance

        • 1. Re: Relation problem
          Stephen Huston

          Subjects --- Grades appears to be a 1:1 relationship.

          Why not grade as a field in subject?

           

          You gave no clue what your key fields were for each relationship. What do you want in your report that you aren't getting?

          • 2. Re: Relation problem
            philmodjunk

            And when you exported this data, was it from a layout based on Students, Subjects or Grades? I can't yet see enough detail to tell why you would get a random association of grades to subject, but the context from which you export the data will also be an important detail here.

             

            Personally, I'd consider this set of relationships:

             

            Students-----<Grades>------Subjects

             

            Students::__pkStudentID = Grades::_fkStudentID

            Subjects::__pkSubjectID = Grades::_fkSubjectID

             

            That way, each grade is linked to both a student and a subject. The assumption on which I am basing this is that for a given subject, you have many students taking that subject and for a given student, they are taking many subjects, this would then be a many to many relationship and I am suggesting a table of grades as the join table linking the two in such a many to many relationship.

            1 of 1 people found this helpful
            • 3. Re: Relation problem
              haider2016

              Thank you Stephen to your reply, the reason of putting grade alone is to reduce the redundancy. I don't want to repeat "Excellent" or "Very Good", for instance, many times.

              again thank you so much

              • 4. Re: Relation problem
                haider2016

                Thank you Phil,

                • 5. Re: Relation problem
                  coherentkris

                  you should also consider that there may be an entity called class/enrolement that needs to be expressed in your schema.

                   

                  each student enrolls in zero or many classes.

                  every class has one and only one subject.

                  each subject can be taught zero or many times.

                  each student receives zero or more grades for each class (unless you are recording only final grade).

                  1 of 1 people found this helpful
                  • 6. Re: Relation problem
                    beverly

                    I've called these "Registrations" instead of "Enrollments", but the principle is the same. Dates, grades, FK keys to student and class are all in there. This even allows the same student to take the same class (different dates), if that is within the rules.

                     

                    This allows a good deal of reporting if you work from the "join" file, because it looks to student and classes. Information can be 'tunneled' both ways, if needed.

                     

                    Beverly

                    • 7. Re: Relation problem
                      haider2016

                      Thank you beverly for your reply