many-many relationships question

[michelleumich]

I am developing a Scholarship database and how my tables are set up
now- it works, but I feel like there is a better way to do it. The
reason I am stuck is because my data has a many-many relationship and
I am a little lost as to how to go about linking my tables becuase of
this. Basically I have two tables- One for each student- their
demographic data and the scholarship they have received (and some
information about the scholarship)- and then another table contains
all of the information about the scholarship- where it comes from,
account data, etc. The scholarship information data in the first table
is all available in the second. I am basically converting years of
unorganized excel spreadsheet data into a database- and with that came
a lot of inconsistencies that I spent a lot of time ironing out. Now
that those are taken care of, I realize that I don't need to repeat
the data- it's bad news for when the scholarship information needs to
be updated. Anyways, this data is a many-many because each student
can have more than one scholarship, and each scholarship can go to
more than one student. The way I have my tables set up now makes it
hard to search through forms consistently-

This is how the tables are set up as of now: [indicates field]
(Instead of using the technical terms in the table, I just explained
the fields if necessary)

Student/Scholarship Table-
[Name]
[A]
[Bunch]
[of
[Demographic]
[Data]
[Scholarship Name]
[Second Scholarship- this column is left blank if the student does not
have one]
[Unique Identification # of the Scholarship they have]
[The Unique Identification # of the second scholarship they have, if
they have a second scholarship]
[Another descriptive # of the Scholarship the student has]
[Another column for the same descriptive #, if the student has a
second scholarship]

Scholarship Information Table-
[Scholarship Name] (Same data as first table)
[Unique Identification #] (same data as first table)
[Another Descriptive # ] (Same data as first table)

I know that there must be a better way to organize this data without a
ton of null values and multiple columns for the same data in the
student/scholarship table. However, I don't know how to go about
linking a many-many relationship. I realize that I probably am going
to have to separate my tables- this is fine as long as there's a way
to include data from multiple fields on forms. Anyways, I hope
somebody can help me.
Thanks,
M

 

[BruceM]

You need three tables. Each table contains information about a single
entity such as student. The Student table, therefore, should not contain
scholarship information, as it is not an attribute of the student.

The missing table is a junction table. I will call it
tblStudentScholarship.

tblStudent
StudentID (primary key, or PK)
FirstName
LastName
etc.

tblScholarship
ScholarshipID (PK)
ScholDesc (description)
Other fields specific to the scholarhip (awarded by, amount, etc.)

tblStudentScholarship
StScID (PK)
StudentID (foreign key, or FK)
ScholarshipID (FK)
AwardedDate
Other fields specific to a scholarship as it pertains to a student

Create a one-to-many relationship between StudentID and ScholarshipID and
their namesake fields in tblStudentScholarship. Make a form based on
tblScholarship, with a continuous subform based on tblStudentScholarship.
The subform contains a combo box based on tblStudent, which stores StudentID
in tblStudentScholarship. The idea is that you go to a scholarship record
and select a student or students to whom it is to be awarded. Create a
separate record for each student.

You can reverse the above, with a main form based on tblStudent and a
subform based on tblStudentScholarship. The combo box on the subform is
based on tblScholarship. It is probably not likely that you would take this
approach for the form, but perhaps for reporting it would make sense, so
that you can have a list of scholarship recipients and the scholarships they
have been awarded. The reporting can wait until the forms are functioning
properly.

Note that you could combine StudentID and ScholarshipID as the multi-field
PK for tblStudentScholarship if you want to assure that a student cannot be
awarded the same scholarship twice (that is, the combination of StudentID
and ScholarshipID is unique). However, if a scholarship can be awarded in
consecutive years to the same student, this is not the approach to take.

I am developing a Scholarship database and how my tables are set up
now- it works, but I feel like there is a better way to do it. The
reason I am stuck is because my data has a many-many relationship and
I am a little lost as to how to go about linking my tables becuase of
this. Basically I have two tables- One for each student- their
demographic data and the scholarship they have received (and some
information about the scholarship)- and then another table contains
all of the information about the scholarship- where it comes from,
account data, etc. The scholarship information data in the first table
is all available in the second. I am basically converting years of
unorganized excel spreadsheet data into a database- and with that came
a lot of inconsistencies that I spent a lot of time ironing out. Now
that those are taken care of, I realize that I don't need to repeat
the data- it's bad news for when the scholarship information needs to
be updated. Anyways, this data is a many-many because each student
can have more than one scholarship, and each scholarship can go to
more than one student. The way I have my tables set up now makes it
hard to search through forms consistently-

This is how the tables are set up as of now: [indicates field]
(Instead of using the technical terms in the table, I just explained
the fields if necessary)

Student/Scholarship Table-
[Name]
[A]
[Bunch]
[of
[Demographic]
[Data]
[Scholarship Name]
[Second Scholarship- this column is left blank if the student does not
have one]
[Unique Identification # of the Scholarship they have]
[The Unique Identification # of the second scholarship they have, if
they have a second scholarship]
[Another descriptive # of the Scholarship the student has]
[Another column for the same descriptive #, if the student has a
second scholarship]

Scholarship Information Table-
[Scholarship Name] (Same data as first table)
[Unique Identification #] (same data as first table)
[Another Descriptive # ] (Same data as first table)

I know that there must be a better way to organize this data without a
ton of null values and multiple columns for the same data in the
student/scholarship table. However, I don't know how to go about
linking a many-many relationship. I realize that I probably am going
to have to separate my tables- this is fine as long as there's a way
to include data from multiple fields on forms. Anyways, I hope
somebody can help me.
Thanks,
M

 

[John W. Vinson]

The
reason I am stuck is because my data has a many-many relationship and
I am a little lost as to how to go about linking my tables becuase of
this. Basically I have two tables-

That's your problem. You need A THIRD TABLE - StudentScholarships - linked to
both these tables.

If student Jane Jones has three scholarships, there would be three records in
StudentScholarships with Jane's unique ID in the StudentID field, and with the
three Scholarship ID's in the three records.

John W. Vinson [MVP]