• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

database with many-to-many-to-many- relationship

Hi - I've been working at this for the longest time and I just can't quite get it to work.

The scenario is that the teacher gives the students of which there are 25, a packet assignment for each week which consists of six subjects.  Each subject is worth 10 points as well as a test worth 40 points, for a total of 100 points possible for that week.


Tables are:

Subject(SubjectID, SubjectName)

Assignment(AssignmentID, AssignmentName, DueDate, PointsPossible)

Student(StudentID, StudentName)

StudentSubjectAssignment(AssignmentID, StudentID, SubjectID, SubmissionDate, PointsEarned)


I'm having the worst time of it trying to make a form showing the assignment, subject, and student with corresponding score.  Please help, all input is appreciated.

Thank you, Brynda
0
Not_By_Default
Asked:
Not_By_Default
  • 3
1 Solution
 
Arthur_WoodCommented:
this table:

StudentSubjectAssignment(AssignmentID, StudentID, SubjectID, SubmissionDate, PointsEarned)


should be two tables

StudentAssignment(StudentID, AssignmentID, SubmissionDate, PointsEarned)

and

AssignmentSubject(AssignmentID, SubjectID)

Thus each student can have many assignments, and each assignemtn is made up of many subjects.  

You NEVER have more than a single combination in any one association table.

Each Association table is designed to decompose a Many-to-Many relation into a pair of One-to-many relations.

AW
0
 
Not_By_DefaultAuthor Commented:
You have saved me countless hours!  It all seems so simple now..  :)

Many thanks, for taking the time out to lend a hand. :D
0
 
Not_By_DefaultAuthor Commented:
I now have another situation..and of course, will give more points for the extra help might you please help?

On the form I have two comboboxes and one subform.  As I move through the records, the assignments and the subjects move correctly, i.e.; Week #1 switches through the subjects as does Week #2 and so on.  However, the subform is only following the subject combobox.  For instance when I go back to Week #2 for English it displays the points from Week #1.  I need to relate the subform to both comboboxes but don't know quite how to go about it.
Thank you, Brynda
0
 
Not_By_DefaultAuthor Commented:
Problem solved!  For reference please see:
http://www.experts-exchange.com/Databases/MS_Access/Q_21222862.html
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now