troubleshooting Question

designing forms for many-many relationship tables

Avatar of ugeb
ugebFlag for United States of America asked on
DatabasesMicrosoft Access
12 Comments2 Solutions368 ViewsLast Modified:
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)?

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros