Many to Many Relationship

[Mike]

Hello,

I have a table with students (studentID) and parents
(parentID). some students have more than one set of
parents (divorce cases), and some parents have more than
one student (siblings). I've created a junction table
with studentID and parentID both as primary indexes.
Now...how do I use this to set up forms/subforms so I can
see everyone relating to everyone else?

Thanks.

Mike

 

[Ken Snell]

Depends upon what you want to display and what you want to be "selecting".

If you want to select a student and then see the parents for that student,
create a form that is based on the junction table, put controls on the form
in the detail section that are bound to the fields in that table, and set
the form to Continuous Forms view. Then turn on the Control Wizard, select a
combo box control, click on the form, and tell the wizard that you want to
use the combo box to look up records on the form. The wizard will give you
choices for how to set this up. Then you can use the combo box to select a
student (for example) and have the student's parents displayed on the form.

 

[Mike]

Ken,

Thanks for your reply. I already have a form that has
students as the main form, and their parents as a
subform. The link field is the studentID, so I have a
corresponding studentID in the parent file. However, in
instances where there are sibling students, I have to
create a duplicate record for the parent(s) in the
parents table so the parents can also relate to the
sibling's studentID number. What I would like is a way I
can simply relate a sibling student to an already
existing parent record. I would also need to retain the
ability to relate more than one parent record to a
student, to accomodate multiple parents in divorce
situations. I hope I've explained that clearly. How do
I accomplish that?

Thanks,

Mike

 

[Ken Snell]

Your form setup is just fine. What you want to do in the subform is use
combo boxes for displaying the parentID value for each record (or display
parent name while combo box is bound to the parentID field).

You don't need to "copy" a parent record. Just use the parentID with the
sibling's studentID as the pairing, just as you have for the first student.
Because the two studentID values are different, you can have the same
parentID value combined with the two different studentID values in different
records.

 

[guixian88]

hi...i'm new here. i was wondering if someone could help me out.

i have a many-to-many r/ship between the tblRadio and tblBlock tables.
so, i created a junction table called tblRadioBlock with the primary
keys of tblRadio and tblBlock as primary and foreign keys.

then, i have another table called tblField, which has a one-to-many
r/ship with the tblBlock, ie one block has many fields. so, i have a
the tblBlock primary key as a foreign key in tblField.

the problem now is, that i can't seem to link the data from field to
block to table when i'm creating a report. whenever i select fields
from these three tables, it won't group properly. if i select tblRadio
and tblBlock fields, the report wizard will group Radio fields then
Block fields. if i select Block and Field fields, it'll group by block
then by fields.

when i select fields from all three tables, i just get one lump of
data, which is not what i want at all. i hope i've stated my problem
clearly. could anyone tell me what i'm doing wrong?

i'm thinking it probably has something to do with the relationships,
since if the relationships are correct, the report wizard will group
them correctly.

 

[Tim Ferguson]

i have a many-to-many r/ship between the tblRadio and tblBlock tables.
so, i created a junction table called tblRadioBlock with the primary
keys of tblRadio and tblBlock as primary and foreign keys.

then, i have another table called tblField, which has a one-to-many
r/ship with the tblBlock, ie one block has many fields. so, i have a
the tblBlock primary key as a foreign key in tblField.

Okay: we have

Blocks RadioBlocks
====== =========== Radios
BlockID ------+---< BlockID ======
Description \ RadioCode >--------- RCode
BlockStats | Frequency
etc | NumTransistors
| Fields etc
| ======
| FieldNumber
+--< BlockID
CropType
etc

the problem now is, that i can't seem to link the data from field to
block to table when i'm creating a report.

I don't understand. What is a Table -- we only have Radios, Blocks and
Fields in the description above?

the report wizard will group Radio fields then
Block fields. if i select Block and Field fields, it'll group by block
then by fields.

The best answer is just Don't Use the Wizards. Start off by creating the
query and get the grouping right on that first. Use the query grid design
window and enter the tables you actually need, and then join them and sort
them. Once you understand your data, then you can create the report -- the
wizard maybe a little quicker than doing it by hand although I usually find
the reverse. But do get the query right first!

Hope that helps


Tim F

 

[Armen Stein]

hi...i'm new here. i was wondering if someone could help me out.

i have a many-to-many r/ship between the tblRadio and tblBlock tables.
so, i created a junction table called tblRadioBlock with the primary
keys of tblRadio and tblBlock as primary and foreign keys.

then, i have another table called tblField, which has a one-to-many
r/ship with the tblBlock, ie one block has many fields. so, i have a
the tblBlock primary key as a foreign key in tblField.

the problem now is, that i can't seem to link the data from field to
block to table when i'm creating a report. whenever i select fields
from these three tables, it won't group properly. if i select tblRadio
and tblBlock fields, the report wizard will group Radio fields then
Block fields. if i select Block and Field fields, it'll group by block
then by fields.

when i select fields from all three tables, i just get one lump of
data, which is not what i want at all. i hope i've stated my problem
clearly. could anyone tell me what i'm doing wrong?

i'm thinking it probably has something to do with the relationships,
since if the relationships are correct, the report wizard will group
them correctly.

Hi,

Your relationships seem reasonable, but I can't tell whether they
reflect the real information you are trying to describe.

If you build a report with Radio, RadioBlock and Block, grouped by
Radio, you'll get the same Block appearing underneath EACH Radio to
which it is related. Then, for each Block, you'll get multiple child
Field records. You can do show these relationships either with Sorting
and Grouping, or by building subreports to show the lower levels.

Radio A
Block 1
Field 11
Field 12
Block 2
Field 21
Field 22
Radio B
Block 2 (note - also appears in Radio A above)
Field 21
Field 22

If this doesn't help, try posting a sample format of what you want your
report to look like.

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com