Design problem

[reno.04]

Hi all

I need to develop database for monitoring/recording student behaviour
can anyone suggest a PK for incident table because Autonumber wont link to
student table

ie I have;

Student (student_ID, FirstName,Surname,address,subject_Code#)
Subject (subject_Code, Subject name, Period, room)

and
Incident( __________ , date, time, description,

Action(___________ , dateofAction, description, resultofAction)

now
1 student can have many incidents
many incidents may be done by many students
many incidents can have many actions

but access say that i cant link student to incident_ID if it is autonumber
same for incident to action

any ideas

 

[Allen Browne]

You have a many-to-many relation between Students and Incidents.

To resolve, it create another table called (say) IncidentStudent, with
fields:
- IncidentStudentID AutoNumber primary key
- IncidentID Number foreign key to Incident.IncidentID
- StudentID Number foriegn key to Student.StudentID
- Descrip Text description of how student was
involved in incident.

On your Incident form, you will have a subform where you can enter the
students in the incident, one per line.

If you wish, you can also place a subform on your Student form, to show the
incidents the student has been involved in.