Hi database experts,
I'm designing a db to store client personal and business information, and I'm trying to figure out how to best design the forms for input. I've got several many-to-many relationships that I broke down into one-to-many tables by use of an associated link table. I'll post next a jpeg of the relationships as I built them.
Essentially, it's as follows:
tblClients (1->oo) tblClient_Parents_LINK (1->oo) tblParents (1->oo) tblParent_Child_LINK (1->oo) tblChildren (1->oo) tblCollege_Child_LINK (1->oo) tblColleges
where (1->oo) means One-to-many, if it wasn't clear. Each of the LINK tables have 2 fields, an ID field to match each of the tables. (e.g. tblClient_Parents_LINK has ClientID and ParentID fields)
A client consists of a set of parents and children. Parents can have many children, and Children can have multiple parents, including biological, step, and adoptive.
If my main form is a Client entry form, I will need to enter the parents and children that represent the client. Can this be done with a standard query, or will VBA code be neede?
I know I can have the Client form based on the Client table, and then a subform based on the LINK table, but how do I enter parents and children? What should I enter first? How can parents and children be automatically associated with each other? Can this be done via a well designed query so that 2 tables are update at the same time?
How do I do this efficiently (& painlessly)?