Many to one relationship

[Swillsy]

Hi Guys & Gals

Someone has just pointed out a fatal flaw with one of my table designs involving incident reporting.
Current Structure:

tblStudents
StudentID
StudentName
etc

tblIncidents
StudentID
IncidentID
IncidentType
etc

At present the StudentID is linked on tables which means that the users can report an incident involving a person. However if more than one person is involved then they would have to repeat the process.

My question is how would I go about having a single incidentID and multiple StudentIDs.

I am guessing I would need a one to many relationship in my design- but how would I go about setting this out on the form i.e. studentID2, studentID3???

Thanks

Swillsy

 

[Rabbie]

Because 1 student can be involved in many incidents and more than 1 student can be involved in a single incident there is a many to many relationship. You need to model this using a junction table so your data structure would look like this

tblStudents
StudentID
StudentName
etc

tblIncidents
IncidentID
IncidentType
etc

TblStudentsIncidents
StudentId
IncidentID
etc

 

[Swillsy]

Thanks for your reply

I understand how the db table setup would look like
I am struggling to see how this could be setup on a form - I'm not sure if its possible tbh.
For example if 2 students were having an arguement - at present I would input 2 records with 2 ids via the form
Ideally I would like to setup a text box so I could enter the 2 ids on the record and they would be linked to the Students IDs.

I'm gonna go think about this over more coffee

 

[georgedwilkinson]

You put the junction table in the subform. Look at Pat's M:M example: Many-to-many example