Absolute Beginner - Relationships/Forms - Multiple Fields

[Bodger]

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

[Steve Jorgensen]

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, be************* ***@hotmail.com (Bodger) wrote:

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

[Steve Jorgensen]

On Sat, 17 Jan 2004 06:20:00 GMT, Steve Jorgensen <no****@nospam. nospam>
wrote:

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.

(I seem to be having trouble with the "s" and "g" keys <g>).