Parent-Child Dual Relationship

[Ziphius]

I have a parent-child relationship. The parent table is Individuals with a primary key of IndivdiualID (auto serial). The child table is IndividualSightings, related to its parent through the primary key IndividualID. Each individual has a full name and its easier for me to work the database using the full names, so the two tables are also related by non-primary key fields, FullName. When I sight an individual, and I know who they are, I simply type their name and through the FullName relationship, automatically assign the proper primary key field. If its a name not in the database (new individual), then I allow the creation of new records via that relationship so that it can create a new IndividualID for that new FullName and I want to automatically populate the primary key fields and the FullName fields so the two tables match. This seems pretty straight forward but I cant' seem to get it to work. Is there a better way to do this?

[bcooney]

I think that you are better off designing the interface to Find in the Individuals table, and provide a portal to Sightings. Full name is not unique. How do you know if you're pulling the only parent that matches on full name and not just the first?

[Ziphius]

I think that you are better off designing the interface to Find in the Individuals table, and provide a portal to Sightings. Full name is not unique. How do you know if you're pulling the only parent that matches on full name and not just the first?

Essentially I conduct a survey and photograph a bunch of individuals. I match the photographs against the catalog to find out who they are, and then enter them into the database. So there is actually another parent table called Surveys where one survey can have many individuals sighted. Within a survey record I have a sighing portal where I add my individuals observed. I guess when I do the matching earlier, i should just right down the unique individual ID and add it to the portal row to create a link back to the individual. If its a new individual, then I guess I could just add the next unique ID number (maybe have a value list of the unique individual ID's where I can scroll to the bottom and see last one used). I realize anyone who really knows what they are doing would cringe at this as a solution, but as long as it works.