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

  • Thread starter Thread starter RMTechie
  • Start date Start date
R

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?
 
RMTechie said:
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?
Click to expand...


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.
 
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
 
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
 
Back
Top