problem in accessing fields from table with multiple foreign keys referencing single primary key

[ravichoudhari]

i came accross requirement of multiple foreign keys in a table
referencing the same primary key another table. i created the table
relations using the relations editor in access. even though i could
have multiple foreign keys to a table access did allow the referential
integrity.

my database structure is

table1 - students table
table 2 - subjects table
table 3 - subjects opted by students

in the table 3 totally there are 6 subject columns and a student can
opt for variable number of subjects.

table structure

students
ID -> primary key, FirstName, LastName, ........

subjects
ID->primary key, subject

student_subjects
ID->primary key, StudentID->foreign key referencing students.ID, and
sub1, sub2, .....sub6 all of which reference the primary key in
subjects table.

now if in the student_subjects table i have a record with data
ID-1000, StudentID-100, sub1-1, sub2-2......sub6-6,

how can i join table in SQL query to access the student_subjects table
so that i can display the result as

ID->studentID (name not required)
but from sub1 to sub6 i should display the actual subject name that is
in the subjects table ?

for example :

ID sub1 sub2 sub3 ....... sub6
1000 telecom. cryptography networking .... cgi/perl


the one natural solution to this which i got from other groups is
restructuring the student_subjects table as

ID->primary key, StudentID-Fk, SubjectID-Fk

The idea is to have many records here, instead of repeating fields. So
if
Student 24 enrols in subjects 2,4, and 7, you have records like this:

StudentID SubjectID
24 2
24 4
24 7

now for a student i have multiple records in the student_subjects
table with different ID (primary key), to view the entire records of a
student as a single record i need to refer to StudentID field.

this can be used to further processing of data, but with this approach
how do i display the data in a flex grid as a single record for every
student as given in above example ?

 

[John W. Vinson]

i came accross requirement of multiple foreign keys in a table
referencing the same primary key another table. i created the table
relations using the relations editor in access. even though i could
have multiple foreign keys to a table access did allow the referential
integrity.

my database structure is

table1 - students table
table 2 - subjects table
table 3 - subjects opted by students

in the table 3 totally there are 6 subject columns and a student can
opt for variable number of subjects.

table structure

students
ID -> primary key, FirstName, LastName, ........

ok... that's good...

subjects
ID->primary key, subject

ok... that's good...

student_subjects
ID->primary key, StudentID->foreign key referencing students.ID, and
sub1, sub2, .....sub6 all of which reference the primary key in
subjects table.

No, that's WRONG.

"Fields are expensive, records are cheap". Your Student_Subjects table should
have only two fields - StudentID and SubjectID. If a student is taking six
subjects you would add six RECORDS to this table, not six fields.

For data entry and display, use a Form based on the Students table (or the
Subjects table, or both), with a continuous Subform based on
Students_Subjects. Use the StudentID (or SubjectID for the second option) as
the Master and Child Link Field, and put a combo on the subform to select the
Subject (or student).

If you really need to see the subjects all in one line, you can use VBA code
to extract them from the table. See http://www.mvps.org/access/search and
search for "concatenate".

John W. Vinson [MVP]