AnsweredAssumed Answered

How to find a record in two related tables

Question asked by HowardRathbun on Sep 29, 2010
Latest reply on Sep 29, 2010 by FentonJones


How to find a record in two related tables


I have two tables with a one-to-many relationship.  I created a layout with  several fields from each table including the field which is common to both tables.  This field contains unique values in the parent table but may contain one or more duplicates in the children table.  When I exit the edit mode in the layout, the layout shows the data from both table as expected.  I can navigate through the records in the parent table and the data in the fields from each tables is displayed correctly.  So far so good.  If I press the find icon, I can enter any valid record number and the record with the related child records is found - also as expected.  The children table has a field which numbers the children from 1 to x for each record number.  Eg. if there is only one child for a particular parent record, then the child number is 1 and if there are 3 children for another parent record, then the children are numbered 1, 2, and 3.  Now when I navigate through the parent records as described above, the layout always displays data from child no 1 regardless of the number of children for each particular record.

The problem I have is that if I do a find using the find icon, and enter a record number into the common field and a child number into its field, the find command fails: it says record not found.  Stated another way, I have not found any method whereby I can navigate through the records and for those parents which have more then one child, also navigate through  (display) them.

I am sure this is possible but the way to do it escapes me.