designing forms for many-many relationship tables
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)?
Thanks!
Gene
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)?
Thanks!
Gene
your client table looks more like an Appointment table. The way it is designed one client can only have one workshop and one consultation etc. does this reflect how your business operates?
ASKER
Hi,
Essentially, yes. Each client is introduced to us at a specific workshop and each gets one free consultation. Later on there may be another table containing dates and notes from meetings, but it's already pretty complex and I want to figure out how to make it work like this before it gets even more complicated. Make sense?
Thanks:)
Essentially, yes. Each client is introduced to us at a specific workshop and each gets one free consultation. Later on there may be another table containing dates and notes from meetings, but it's already pretty complex and I want to figure out how to make it work like this before it gets even more complicated. Make sense?
Thanks:)
Also, your design indicates that a client must be a parent. how would a client (say a mature adult) with no parents be handled?
do you have one to one (i.e individual ) consultations or are they always group (ie. family)
if its always family then you may want to create a ClientGroup or Family table and then link that to a FamilyMember table that have PersonID PK and a FamilyID FK to link back to the Family table.
do you have one to one (i.e individual ) consultations or are they always group (ie. family)
if its always family then you may want to create a ClientGroup or Family table and then link that to a FamilyMember table that have PersonID PK and a FamilyID FK to link back to the Family table.
ASKER
For our services, we can only help students who are dependents, so having a parent (or guardian) is a must. If, later on, that requirement changes, then I suppose I could add a "null" parent as a place holder.
The free consultation is done per family, regardless of who in the family attends. Once a parent (mother or father) uses their consultation, it is used up.
I thought about creating a family table, but I felt like the client table would mimic that behavior without the need for another table. There's also the issue of one child having divorced parents who are remarried, so essentially separate families. It's also possible that a parent might have 2 families, one currently and one from a previous marriage.
I'm really trying to figure out how to input this info into a form, and what kind of query would allow simultaneous updates of all relevant tables.
The free consultation is done per family, regardless of who in the family attends. Once a parent (mother or father) uses their consultation, it is used up.
I thought about creating a family table, but I felt like the client table would mimic that behavior without the need for another table. There's also the issue of one child having divorced parents who are remarried, so essentially separate families. It's also possible that a parent might have 2 families, one currently and one from a previous marriage.
I'm really trying to figure out how to input this info into a form, and what kind of query would allow simultaneous updates of all relevant tables.
i have a urgent production problem to attend to. taking your db design as final, i suggest that you unbound forms and vaildate the data before inserting into the real tables. To make it robust with proper error handle i suggest you need to write some vba code to execute the validation. let me know if you need some help.
ASKER
Sorry, but I understand generally how to do things. I need much more specific direction on getting data into forms and queries. My original question still stands
"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 needed?
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)?"
I'm afraid none of these questions were answered.
"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 needed?
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)?"
I'm afraid none of these questions were answered.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
this site does not recognise my default font that i use, above should read
Sql = insert tblClient_Parents_LINK (ClientID, ParentID) select & me.txtClientID & , & lngParentID
Sql = insert tblClient_Parents_LINK (ClientID, ParentID) select & me.txtClientID & , & lngParentID
3rd attempt
Sql = "insert tblClient_Parents_LINK (ClientID, ParentID) select " & me.txtClientID & "," & lngParentID
Sql = "insert tblClient_Parents_LINK (ClientID, ParentID) select " & me.txtClientID & "," & lngParentID
ASKER
Thanks for the answers.
For your benefit, the best discussion of this type of design is in the book:
Expert One-On-One Microsoft Access Application Development
which goes into great detail on designing these tables, queries, and forms. I really recommend it.
Thanks again.
For your benefit, the best discussion of this type of design is in the book:
Expert One-On-One Microsoft Access Application Development
which goes into great detail on designing these tables, queries, and forms. I really recommend it.
Thanks again.
ASKER
https://filedb.experts-exchange.com/incoming/ee-stuff/4350-Database-Table-Relationships.jpg