OK, you have a table of students, a table of universities, and a junction
table of combinations of students and universities.
If you want to see all the universities for a student, you need a parent form
for student, and a subform based on the junction table, usually showinf the
associated univerities by way of combo boxes bound to the univertity id
column, but it could be done by joining to that table in a query.
To see all the students for a university, you turn that upside down. The
master form is bound to the univertities table, the subform is bound to the
junction, and the student data for each of these is shown with a combo box or
using a query join.
Note that the primary key of the junction table should be the combination of
the keys for the other 2 tables, so each combination of student and university
represents exactly one record in the junction table.
Does this help?
On 16 Jan 2004 21:56:48 -0800,
benjaminlukemartin@hotmail.com (Bodger) wrote:
[color=blue]
>Hi,
>
>Bearing in mind that I am new to this game, understand very little
>about SQL and don't work in IT, I was wondering if someone could give
>me some friendly advice on how to design relationships and/or forms
>for the following tables:
>
>1) 'Students' - One record per student. Student can choose six
>different universities, so as well as all of the other student
>information, the table has six fields (called 'University 1',
>University 2', etc)
>
>2) 'Universities' - One record per university. One university can take
>many different students
>
>What I would like is that when I view a 'University' form, I can see
>details of all students associated with that university and when I
>view a 'Student' form, I can see details of all universities with
>which that student is associated. From what I can gather, another
>table to serve as a junction box is needed but when I made one, only
>the information in the field 'University 1' was displayed from the
>student table; the data in fields 'University 2', University 3', etc.
>didn't appear. Other relationships and forms in the database (staff,
>offices, agents,etc) seem to be working OK - just can't figure out
>this multiple-field-same-value relationship stuff. I've had a look
>around this group for something I can understand that might be
>relevant but I can't seem to apply other advice to my situation. A
>little knowledge is indeed a dangerous thing.
>
>I realise that this is basic stuff and I apologise in advance for my
>ignorance.
>
>Thanks,
>
>Ben[/color]
