Many to Many 1

[spartansFC]

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

http://www.databasedev.co.uk/nursery_playgroup_data_model.html

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

 

[lameid]

Your difficulty with number 2 is that both children and parents are People. This raises the question should you normalize that far and have your junction table define the parent/child relationship among people? And then there is the situation where Mom has house A and Dad has house B and child goes between the two. Which is the child's address? Doesn't the child have more than one address? Does this mean it makes sense that only parent's have addresses?

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).

 

[spartansFC]

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

 

[lameid]

You might consider having an address table so that you enter an address once. Then you would have another junction / bridge entity table to for each parents and children. If you do that you could then have a couple queries to move everyone from one address to another.

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.

 

[spartansFC]

thats a great idea to have an address table, i have a postcode table which i've used on other databases but i could have:

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?

 

[lameid]

Similar... You calling it a Family address implies all the people are members of a family entity somewhere.

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.