Displaying records that have one of two fields equal to Master Field RMTechie <srusskinyon[ at ]gmail.com> 12/17/2008 6:48:44 PM 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?

Re: Displaying records that have one of two fields equal to Master Field Marshall Barton <marshbarton[ at ]wowway.com> 12/17/2008 7:04:10 PM RMTechie wrote: [Quoted Text] >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. -- Marsh MVP [MS Access]

Re: Displaying records that have one of two fields equal to Master Field John Spencer <spencer[ at ]chpdm.edu> 12/17/2008 7:37:20 PM 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 RMTechie wrote: [Quoted Text] > 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?

Re: Displaying records that have one of two fields equal to Master Field John Spencer <spencer[ at ]chpdm.edu> 12/17/2008 7:40:43 PM 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 RMTechie wrote: [Quoted Text] > 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?