Adding New Fields Automatically?

[jmayojr]

Hello everyone, I'm in the process of developing a database for work that will be used to track conduct records for students. My ideal setup is to have the users (my staff and I) create a record for each incident, then associate each student to that incident using their ID numbers. My question is regarding my Incident Table. It's set for basic information collection, the what, when where of the incident. I want to also add the who (students) when I create the record. The student's information (StudentID, Name, etc) lives in my Student information table. My question is this: is there a way to have access create fields as I add ID numbers, or would I have to have the table preset with a number of fields like "Student1" "Student2" etc? I appreciate the help.

[pbaldy]

You do not want fields like "Student1" "Student2", you want a related table:

Fundamentals of Relational Database Design -- r937.com

[jmayojr]

Thanks Paul. I read through that resource and it's helpful. I think I got the design. If I follow a second normal form style it might work. Something like this...

tblIncidents
IncidentID - pk
IncidentDate

tblStudents
StudentID - pk
StudentFirstName
StudentSecondName
StudentEmail

tblIncidentDetail
IncidentID - pk
StudentID - pk
IncidentLocation
Violation (this pulls from tblViolations as a Lookup/Combobox right now)

This way each incident can have more than one student associated with it. Ideally my reporting would be by incident with details on each student, then another report for each student that lists all the incidents they've been a part of.

[pbaldy]

Looks good. The third table is what's often called a many-to-many junction table. A student can be have many incidents, an incident can have many students.

[jmayojr]

Thanks again!

Looks good. The third table is what's often called a many-to-many junction table. A student can be have many incidents, an incident can have many students.

[pbaldy]

Happy to help and welcome to the site by the way! To clarify, I assume the location can vary by student? If not, it should be field in the incidents table.

[jmayojr]

Location will vary by incident for reporting standards, either on-campus, off-campus, or adjacent to campus possibly others at a future point.

[pbaldy]

If it only varies by incident, it should be a field in the incidents table, not the junction table.

[jmayojr]

Ok, here is the table layout with relationships.... let me know what you think... Thanks!

The basic goal is to have the database track incident information and hearing information and outcomes.

Relationships for New Conduct Database.pdf