Relation between tables

[Telesphore]

In the table of the students [tblStudents] of our school containing the
fields of the identification of the students, we had fields concerning the
diplomas obtained.

To reduce the table of the students, we decided to create a new table
[tblDiplomas] with the fields which existed in the table of the students.

However probably for a problem of relations between the old one and the new
table, by adding a new student in the table of the students, we realized
that it did not have his equivalent in the table of the diplomas:
tblStudents = 663 students, tblDiplomas = 662.

tblStudents with the primary key StudentID, plus the fields Name, Street,
City, etc.

tblDiplomas with the primary key DiplomaID, plus the fields StudentID,
Number, Note, Mention, etc.

It has a relation One-to-many with the application of the referential
integrity between the primary key StudentID of tblStudents and the StudentID
field of the tblDiplomas.

Thank you for any suggestion.

 

[pietlinden]

In the table of the students [tblStudents] of our school containing the
fields of the identification of the students, we had fields concerning the
diplomas obtained.

To reduce the table of the students, we decided to create a new table
[tblDiplomas] with the fields which existed in the table of the students.

However probably for a problem of relations between the old one and the new
table, by adding a new student in the table of the students, we realized
that it did not have his equivalent in the table of the diplomas:
tblStudents = 663 students, tblDiplomas = 662.

tblStudents with the primary key StudentID, plus the fields Name, Street,
City, etc.

tblDiplomas with the primary key DiplomaID, plus the fields StudentID,
Number, Note, Mention, etc.

It has a relation One-to-many with the application of the referential
integrity between the primary key StudentID of tblStudents and the StudentID
field of the tblDiplomas.

Thank you for any suggestion.

If you ask an explicit question, you're much more likely to get an
answer...
You mean "Show me the student who has no diploma?"

SELECT * FROM tblStudent LEFT JOIN tblDiploma ON
tblStudent.StudentID=tblDiploma.StudentID
WHERE tblDiploma.StudentID IS NULL;

if that doesn't do it. explain further. How about phrasing it as a
question? Just makes it easier to identify.

 

[Arvin Meyer [MVP]]

You've actually created a second table that has a one-to-one relationship.
It is a rare case where that is necessary. If is possible to have a student
with multiple records in the Diploma table, you would have a one-to-many
relationship. Records are added in the second table through a form/subform
link. Look at the Northwinf sample database that came with Access for an
example.

 

[John W. Vinson]

However probably for a problem of relations between the old one and the new
table, by adding a new student in the table of the students, we realized
that it did not have his equivalent in the table of the diplomas:
tblStudents = 663 students, tblDiplomas = 662.

tblStudents with the primary key StudentID, plus the fields Name, Street,
City, etc.

tblDiplomas with the primary key DiplomaID, plus the fields StudentID,
Number, Note, Mention, etc.

It has a relation One-to-many with the application of the referential
integrity between the primary key StudentID of tblStudents and the StudentID
field of the tblDiplomas.

A "one to many" relationship actually means "one to (zero, one or more)".
Adding a record to the Student table would not automagically cause a record to
be added to Diplomas - you wouldn't WANT it to, because a new student would
probably have no diploma! Also, might not a student have more than one
diploma?

Normally one would use a Form based on the students table, with a Subform
based on the Diploma table, using StudentID as the master/child link field.
This would let you add a record to the Diplomas table, with the student ID of
the currently selected student, whenever you have information to put into the
diplomas table.

If you are expecting that a Relationship will create a new record... it won't.
All the relationship does is to *prevent* you from adding a diploma for a
nonexistant student.

John W. Vinson [MVP]

 

[Telesphore]

Thank you all.

I understood that as long as a new student did not obtain a diploma, it is
normal that the numbers do not correspond in the table's students and
diplomas.

The other problem it is that the sub-form did not appear because its source
was the query qryDiplomas instead of the table tblDiplomas.

Telesphore

 

[John W. Vinson]

The other problem it is that the sub-form did not appear because its source
was the query qryDiplomas instead of the table tblDiplomas.

You have not mentioned qryDiplomas, nor described its structure, so I really
cannot comment - other than to say that typically the mainform would be based
on the "one" side table (students) and the subform on the "many" (diplomas).
It's certainly not necessary nor appropriate to base either form on a query
joining the two!

John W. Vinson [MVP]