Displaying records that have one of two fields equal to Master Field

[RMTechie]

I have both parents and children in a table (with unique ID numbers).
I am making a form that will display a parents name and then all
children related to that parent. In order to do this, I made two
fields for each child named "Relationship1" and "Relationship2" that
contain a parent's ID number.

I can successfully make subforms that match the parent's ID to the ID
number in Relationship1 OR ID number to Relationship2. However, I
would like to display the form if the parent's ID number occurs in
EITHER field. As far as I can tell, you can't like two child fields
to one master field. Is there another way of doing this?

 

[Marshall Barton]

I have both parents and children in a table (with unique ID numbers).
I am making a form that will display a parents name and then all
children related to that parent. In order to do this, I made two
fields for each child named "Relationship1" and "Relationship2" that
contain a parent's ID number.

I can successfully make subforms that match the parent's ID to the ID
number in Relationship1 OR ID number to Relationship2. However, I
would like to display the form if the parent's ID number occurs in
EITHER field. As far as I can tell, you can't like two child fields
to one master field. Is there another way of doing this?

You can simulate the Link Master/Child links by using
criteria in the subform's record source query.

Relationship1 = Forms!mainform.parentid OR Relationship2 =
Forms!mainform.parentid

Then whenever you navigate to a different main form record
(i.e. different parentid), requery the subform (probably in
the main form's Current event.

 

[John Spencer]

You can link the fields to separate instances of the parents table.

Add the parents table to the query twice. Link Relationship1 to one instance
of the table and Relationship2 to the other instance.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

A better design would be a many to many join table. The added table would
contain the ParentID and the ChildId and possibly the type of relationship
(father, mother, guardian, mother biological, mother other, etc).

You would have one record in this table for each combination of adult and
child. So in two-parent households you would have two records for each child
in the household.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County