Hi,
I'm in the process of creating a new database to hold all the student conduct records (relationship chart is below). So far it's working well, but I have one section that I am stuck at and I'm hoping that the combined brain power can help me move forward.
The database work flow is as follows:
- Incident occurs and is reported
- Incident details are input on frmIncidentOverview with unique IncidentID's
- Students are then added to the incident by StudentID using frmIncidentDetail.
- Hearing date/time, violation(s), hearing officer are all then updated on fromIncidentOverview when students are added
- Meeting occurs
- Incidents are then updated in the database with hearing outcomes and sanctions.
- Cases are closed when sanctions are completed or outcome dates are satisfied (ex: probation until 12/31/17).
Notes:
Each incident can have more than one student involved
Each student can have more than one incident
Each student can have more than one sanction for each incident
Where I'm stuck
I'm having trouble wrapping my brain around adding multiple sanctions for each student. Sanctions also need to include due date and completed date (or completed yes/no)
Any and all help or feedback would be appreciated.