Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Many to Many 1

Status
Not open for further replies.
spartansFC

spartansFC

Programmer
Apr 1, 2009
165
GB
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
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
 
Sort by date Sort by votes
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).
 
Upvote 0 Downvote

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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MrStohler
  • Locked
  • Question
Beginner Q: How to separate data from Excel into related tables and maintain relationship? 1
Replies
5
Views
706
SkipVought
S
ameedoo3000
  • Locked
  • Question
Send a message to an email
Replies
2
Views
4K
ameedoo3000
ameedoo3000
MattGreer
  • Locked
  • Question
Managing Several Many-To-Many Relationships 1
Replies
8
Views
379
MattGreer
MattGreer
monkeysee
  • Locked
  • Question
Field to count specific records
Replies
3
Views
646
dhookom
dhookom
BugZap13
  • Locked
  • Question
How to extract Access table schema with decimal places on numeric fields?
Replies
2
Views
4K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top