By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 1,032 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

Absolute Beginner - Relationships/Forms - Multiple Fields

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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


Nov 12 '05 #2

P: n/a
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>).
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.