Many to Many
Many to Many
(OP)
Hi
i've created 3 tables which i've linked via a junction table, although i'm not sure if i've done it correctly as i'm not 100% sure of many to many relationships, i'm using a structure i found from ht
it fits everything i need although i still need to work out the addresses. My basic structure at the moment is:
tblParent
lngParentID
strParentForename
strParentSurname
dteParentDOB
lngParentRelationship
tblParentChildDetails (junction table)
lngParentID
lngChildID
tblChild
lngChildID
strChildForename
strChildSurname
dteChildDOB
So i've linked the tables together via relationships, tried to create the form but when i do that it shows the tblParent data as a form and tblParentChildDetails as a subform but not tblChild.
some of my problems are:
1. i need to have the parents as a continuos form aswell but how do i create this, when i create the form i can set it as a single/continuous form but i've always had a problem working out how to create continuous forms as the main form which are then linked to other continuos forms. Do i create 3 seperate forms, add then to a blank form and link them that way.
2. How would i add a table, so that each person can have different addresses. Would i add for example (strAddressLine1, strPostcode) to the junction table.
This is probably quite easy for most but i'm struggling.
Any ideas
Michael
Talk To Other Members
RE: Many to Many
Not all these situations are necessarily relevant to what you are doing but should shape the structure of your database.
Now back to number 1, which is better suited to the forms forum...
If parents and children are separate tables, I would have 2 screens. One where you enter Parents and associate children to them and another where you enter Children and associate parents to them.
If you have a Person(People) table then you would have to allow toggling the parent and child for entry for the selected relationship (and changing the relationship list accordingly). This is more involved and if you think this is the way to go and want more help, please post in the form forum with a link to the thread here (copy the word thread and numbers with a dash towards the top left).
RE: Many to Many
Thanks for the reply, although it's probably a good idea for only the parents to have addresses, i've been asked to allow the database to record the addresses for everyone. If the child went inbetween 2 houses which could happen, we'd make the decision as to where the actual address is. I'd also like to keep a history of each person's address history incase they move etc.
i'll copy item 2 and move it to the forms forum
thanks again for your help
RE: Many to Many
The tables that map people to addresses should have effective dates in them... So no end date should be the current address. If you need an address for a particular time, you should be able to find it too.
RE: Many to Many
tblAddresses
lngAddressID
strHouseNo
strStreetName
strAddressLine3
strTown
lngPostcode (linked to tblPostcodes)
the user would manually update this table and enter individual addresses as and when
i could then create tblFamilyAddresses
tblFamilyAddress
lngFamilyAddressID
dteFamilyAddressMovedIn
dteFamilyAddressMovedOut
lngFamilyAddressDesc (linked to tblAddresses)
is this what you had in mind?
RE: Many to Many
Not saying your idea is bad but I was thinking the similar but one table for parents and one table for children... Then you could have them all move at once with by inserting and updating all like addressId's. Although a Family table may be more normalized. I expect you only care what the address of the family is in your situation and not each individual so your table may suit your situation better. I was going down the other path because each individual has to have an address... which in my mind means different.
The catch with a Family address is that people have to know that when Mom's place is the home address they do not update with Dad's address.