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

Posted on 2004-11-28
Last Modified: 2009-07-29
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
Question by:Not_By_Default
    LVL 44

    Accepted Solution

    this table:

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

    should be two tables

    StudentAssignment(StudentID, AssignmentID, SubmissionDate, PointsEarned)


    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.


    Author Comment

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

    Many thanks, for taking the time out to lend a hand. :D

    Author Comment

    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

    Author Comment

    Problem solved!  For reference please see:

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now