Many-to-Many relationship? (1 Viewer)

[Toolman]

Hello,
A little background: I started to build a database for children in our speech department.
I built a PatientT, with PatientID, Lastname. Firstname, (and other stuff).
I built a RelationshipT, with RelationshipID, Relationship. The idea being
we have to notify the parents, and sometimes stepmothers,
Father'sPartner and stuff like that.
Then I have the ParentT, with ParentID, PatientID, RelationshipID, Lastname, FirstName

This worked great. Each child was unique, and could have multiple parents. I had a PatientF, with a ParentSubForm.

Now a kink has come up. I didn't figure on a family having multiple children on program!! So I have a real life example of Bobby, Julie, and David Smith all linked to the same 2 parents. But there is no way I can use the ParentT as is because I would have repetitive instances of the same parents for different children.

Can you please make a suggestion? It crossed my mind that this is similar to the junction table (OrderDetails) in the Northwind sample database in Access 2003. But I've never encountered this before (I am new at this) and I could use some guidance/reassurance on what to do.

Thanks for your help,
Don

 

[spikepl]

PatientT
--------
PatientID
FirstName
LastName
stuff


ParentT
--------
ParentID
FristName
LastName
stuff

RelationsT
----------
RelationID
PatientID
ParentID
RelationTypeID


RelationTypes
-------------
RelationTypeID
RelationType (mom, dad, step... etc)

with the above you can link arbitrary number of patients with arbitrary number of parents

 

[Toolman]

Thank you for your quick reply!

Sorry to be a dummy, but how do I set up the relationships? I think it might be:
PatientT.PatientID to RelationT.PatientID
ParentT.ParentID to RelationT.ParentID
RelationsTypeT.RelationsTypeID to RelationT.RelationTypeID

One to Many

All one-to-many?


Thank you again for your help.
Don

 

[stopher]

Thank you for your quick reply!

Sorry to be a dummy, but how do I set up the relationships? I think it might be:
PatientT.PatientID to RelationT.PatientID
ParentT.ParentID to RelationT.ParentID
RelationsTypeT.RelationsTypeID to RelationT.RelationTypeID

One to Many

All one-to-many?

Yes, that is correct.

 

[Toolman]

I beg for help one last time.

I built a test db per spikepl's specs. I set up the relationships as recommended. I added 6 fake patients to PatientT, and 12 fake parents to ParentsT. But I have a data entry problem now.

How do I populate the junction table RelationsT? Or rather the db user. As it stands now when a new patient comes on program they would have to 1) enter the child's first & last name, then the parent's first & last names, then do it all again to populate the junction table. This seems labor intensive.

Does someone have a link to refer me to or some other answer?


Again, I thank you for your help.
Don