Tables and Relationships - Access 97 1

[Caron]

I am writing a student information database with the following tables and am having major trouble setting up the relationships:

Student Demographics ('student ID', last name, first name, etc.)
Family Information ('family ID', female guardian, male guardian, address, city, state, zip, etc.)
Discipline Reports ('auto number', incident type, incident description, date, time, reported by, etc.)
Support Services ('auto number', Social Worker, Special Education, Remedial Reading, Remedial Math, etc.)

One student could have many discipline reports, and the problem I am having is connecting those two tables. I do not understand the process of assigning an appropriate Primary Key and a Foreign Key and establishing a one-to-many relationship. I want to have a form that would find the student by name and then allow you to enter the information about the discipline report.

One family could have many students, and I also need to connect these tables. Do I need to put the Student ID field into the Family table?

Any help is greatly appreciated. Please be explicit in your directions...I'm a newbie.

Thank you!

 

[tlbroadbent]

As you've suggested, you establish the relationships between tables with keys - primary and foreign. For example, the relationship between families and students could be established by placing the FamilyID on each student record. That way you can establish a one-to-many relationship between families and students.

Likewise, place the StudentID on the Discipline table to establish the relationship (one-to-many) between students and disciplinary actions.

Having established relationships you can JOIN the tables in queries.

Select s.StudentID, LastName, FirstName, FamilyID, PhoneNo, IncidentType, IncidentDescription, IncidentDateTime, ReportedBy
From StudentDemographics As s Left Join DisciplineReports As d
On s.StudentID=d.StudentID
Where LastName='smith' Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin