Many to Many 1

[spartansFC]

Hi

I originally posted my question in the table/relationship thread and although part of it was to do with relationships etc some of it was to do with forms so i was asked to post it here, the link to the original thread is thread700-1681437

 

[spartansFC]

i wasn't sure if should explain the question here aswell even though i've posted the link to original thread.

I'm sure someone will tell me

Michael

 

[MajP]

LameID points out some other ways to organize this, and if it was me I agree and would probably only have a single person table and an address table. But to describe how to make a form, lets just assume your tables as posted.

1) Make a continous form based on some select query of table parents.
2) Make a continous form based on a query of table children join to the junction table by child ID.
3) Make an unbound main form
4) add both forms as subforms
5) in the child subform hide the parent ID and use a combo for the child ID of the junction table. This combo should have a rowsource of the children. It should be bound to the child ID, but formatted to show the child name. Something like
Select childID, childLastName & ", " & childFirstName, from tblChild order by childLastName

column count: 2
bound column:1
column widths: 0; 2"

6) Put a hidden text field called txtLink on the main form
7) in the parent subform on current event
me.Parent.txtLink = me.ParentID
(I am guessing at the field name because I am too lazy to go to the other post)
8) In the child subform
link Master fields: [txtBxLink]
link child fields: parentID

 

[MajP]

When you add the subforms the wizard might complain. Ignore or cancel.
And this should read
childLastName & ", " & childFirstName AS childFullName

 

[MajP]

One more thing. If you can figure that out then you can reverse it on the same form. Like LameID suggested.
1) Add buttons "assign child to parents" and "assign Parents to child"
2) So when you pick "Assign Parents to Child" you would change the subforms or just the recordsources of the subforms. So the top continous form is all the children, and the bottom form would show the parents of the selected child or allow you to choose a parent.

 

[spartansFC]

Hi Majp

If i was going to use the better option of having a single person table and single address table linked by a junction table.

It would be better as 1 form would show all the family in one group and any other people connected to the family aswell. Then i could have a different address for each person.

So i've created a subform based off the new tblPeople table, added the subform to a unbound form, it works great, i can add as many people in the family as i like but how do i:

1. Add a brand new family, the unbound form doesn't allow me to move to the new record so i did try your method:

6) Put a hidden text field called txtLink on the main form
7) in the parent subform on current event
   me.Parent.txtLink = me.ParentID

but it doesn't work properly.

the new tables are:

tblPeople
lngPeopleID
lngPeopleTitle
strPeopleForename
strPeopleSurname
lngPeopleRelationship (combo box for child, parent etc)
dtePeopleDOB

tblFamilyAddressJunction
lngPeopleID
lngFamilyAddressID

tblFamilyAddress
lngFamilAddressID
dteFamilyAddressMoveIn
dteFamilyAddressMoveOut
lngFamilyAddressDesc
lngFamilyAddressPostcode

 

[MajP]

That method's purpose is to allow you to synch two continous subforms.

If you were going to do "families", then you are going to have to have someway to identify a family and store it. Then you would relate people to the family. So you would need a family table family ID could be the family head of house hold. Not sure how you plan to handle divorced/remarried.
So if you want to have a continous form up top it would be a list of families. Maybe something like

Family Subform
Mr John Smith Family (selected record)
Ms Jane Doe Family
....

Family member Subform
Mr John Smith Parent
Ms Sarah Smith Parent
joe smith Child
sue smith Child



If sue smiths biogical mom is Jan Doe but she is living sometimes with her dad and sometimes with her mom, you can add her to both families.

But the way the link works if I click on the Ms Jane Doe Family record, the txtbx link stores that family id. The family member subforms links to the text bx and shows all the members in the Jane Doe Family.

 

[MajP]

This demo shows how to synch multiple subforms

http://www.4shared.com/office/_pdcsTfa/SubForms_1_.html

The top form is a continous form for orders
The middle form is a single form view of an order
The lower form is a continous form for order details.

If you select a record in the first subform, the middle synchs to that record and shows the information for that order, the lower subform shoes the associated records to the selected order.

However, this is all done with one line of code in the first subform. All it does is set the PK into txtbxlink. The other subforms link to the textbox.

 

[lameid]

I feel like the forms issue is trying to be addressed before the tables are settled.

I was thinking something more along the lines of below for tables to avoid families. This structure allows more than one address to be considered 'home' for the child but not the parent. In this case you would only need populate tblPersonAddress on the parent side. So if a child has not become a parent, you would not allow associating an address.

Or you could take a more involved family approach and deal with multiple parents/guardians related to each family and many children. That would be more normalized but I do not see any value in doing it that way and can be cumbersome with step and half siblings.

Pehaps instead of Family you mean household? That may be better... Then you can have members of a household and a relationship of parent to child as a separate entity (table). Then you have parents in the household and the child's actual parents. People relationships are some of the most difficult things to manage. I just don't want to be the guy that has to build it

tblPeople
lngPeopleID
lngPeopleTitle
strPeopleForename
strPeopleSurname
lngPeopleRelationship (combo box for child, parent etc)
dtePeopleDOB

tblParentChild
lngParentID (FK to People lngPeopleID)
lngChildID (FK to People lngPeopleID)
blAdressofRecord - a flag that says this is the parent whose address the child belongs at for tracking purposes. In this way the relevant address(es) can be contacted.

tblAddress
lngAddressID
lngFamilyAddressDesc
lngFamilyAddressPostcode

tblPersonAddress
lngPeopleID
lngAdressID
dteAddressMoveIn
dteAddressMoveOut

 

[MajP]

I definitely agree you need to figure out the table structure first. But the concept I describe should work for almost any many to many relationship and can be expanded to a chained many to many to many.

 

[spartansFC]

Now i'm getting really confused, database design is sometimes the most difficult.

so on:

tblParentChild
lngParentID (FK to People lngPeopleID)
lngChildID (FK to People lngPeopleID)
blAddressofRecord

i've seen on another database, where the flag description says "Lives with" and the user can put a "Y" or "n" which means the child lives with the parent and therefore the relevent address(es) can be contacted or am i totally not understanding this.

tblParentChild is the junction table isn't it? so how does tblAddress link into this, would you not have another lngParentChildAdressID in the junction table so that each person could have their own address history?

 

[lameid]

In this scenario the field I put in blAddressofRecord is basically the same as lives with.

For each person to have his own adrress history the junction table for the address has to be related to the person which is why I put the junction table like so.

tblPersonAddress
lngPeopleID
lngAdressID
dteAddressMoveIn
dteAddressMoveOut

Looks like I blindly copied your tblAddress table above.. Obviously it should be address information and not reference 'Family'.

What I suggested though is that children do not have to have an address specified, only parents do.


So all childrens' addresses would like something like...

Select Child.*, tblAddress.*
From tblPeople as Child
  Left Join tblParentChild
	On Child.lngPeopleID = tblParentChild.lngChildID
  Left Join tblPeople as Parent
	On tblParentChild.lngParentID = Parent.lngPeopleID
  Left Join (Select * From tblPersonAddress where dteAddressMoveout is null) as CurrentAddress
	On Parent.lngPeopleID = tblPersonAddress.lngPeopleID
  Left Join tblAddress
	On CurrentAddress.lngAddressID = tblAddress.lngAddressID
Where tblParentChild.blAdressofRecord = Yes

Even though inner joins should work, I tend to always use Left Joins in complex structures with juntion tables so any incomplete information is more likely to turn up.

Hopefully that makes some sense.

 

[spartansFC]

The database design has been made a lot easier as i only need to keep an address history for the children so with that in mind the table design might be changed to:

tblChild
lngChildID
strChildForename... etc

tblAddressLink
lngChildID (FK to tblChild.ChildID)
lngAddressID (FK to tblAddress.lngAddressID)
ysnCorrespondenceAddress

tblAddress
lngAddressID
dteMovedIn
dteMovedOut

to group all the family together as a group (household)i would use

tblRelative
lngRelativeID
lngChildID (FK to tblChild.ChildID)
lngParentID (FK to tblPeople.lngParentID)

tblPeople
lngParentID

so if i wanted to run a report/query to see the family unit i would use tblRelative which groups everyone together.

It's pretty much the same design that lameid suggested or pretty close to it.

So as far as the table/relationships i think i've solved that problem?

Lots more left to do, lets hope the form design goes a bit easier unless someone finds a problem with table design

Michael

 

[lameid]

I would move the date columns for moving in and out to the tblAdresslink table.

You may have multiple children or foster children coming in and out of an address or children moving out of an address temporarily to return later.