Student Reporting Database

[Andrew Redfern]

I am trying to solve a problem around the forms in my database whereby I
want

Student Details at top (name, class etc)
A list of outcomes to be graded for the student
An option group beside each outcome to give the grade (1 of 3 options)

The tables are

Students: StudentID
FirstName
Surname
Class


Outcomes: OutcomeID
Subject
Outcome

Data: DataID
StudentID
OutcomeID
Level

I am able to send a file of what I have done but it is driving me insane. I
think my problem lies in the database design rather than the form.

Cheers

Andrew

 

[Roger Carlson]

Well, the answer depends a great deal on the business rules for your system.
From what you have listed below, I'd say you have a Many-to-Many
relationship between Students And Outcomes. That is:
Each Student may have one or more Outcomes
and
Each Outcome can apply to one or more Students

Is this correct? If so, there is nothing wrong with your design. Your
linking (or intersection) table is built correctly to resolve the M:M into
two 1:M relationships just as it is supposed to.

If the two sentences above are NOT correct, then you have a design flaw in
your database. For instance, if each Outcome applies to One and Only One
Student, they you have a 1:M relationship and you should put the StudentID
field in the Outcomes table and create the relationship.

Now, let's assume the two questions above ARE correct and you do have a M:M
relationship. How can you create a form to work with this. The usual way
is to create a Main form based on one of the tables (say Student) and create
a subform based on a JOIN of the other two (Data and Outcomes). It is VERY
important to make sure that the join field (OutcomeID) listed in the query
is from the linking table (Data) and not the main table (Outcomes).

On my website is a small sample database called
"ImplementingM2MRelationship.mdb" which illustrates how to do this. Look at
Case 2.