what to expect with relationships?

[enriquemellado]

Hi
here is my question, and before someone says to search the forum, i did and can't find the answer... so i'll give it a shot with this thread

I have 2 tables.
1 has personal data: name, birthdate, sex... called tblmemberdata
1 has contaact data: address, phone #'s... called tblmembercontact

i did it that way because my db deals with members, and families, so addresses and home phone # are reapeated for every family member, but every family member needs it's own record.

i created a control id called IdAdress in the tblmemberdata that is related to the IdAddress which is the autonumber field, primary key of rhe table tblmembercontact that carries the adresses.
it created a one-to-many relationship.
which is what i'm looking for
each member has one address
but each address can have more than one member.

when i check the tables after trying with some member info.
i see that the addresses are repeated. the only thing different is the member id that is related to it.
but... doesn't this mean that the address is still being duplicated avery time a new member is related to it?
is this the way the relationship should work?
or am i doing something wrong.
thanks

 

[The_Doc_Man]

People often misunderstand the purpose of one/many relationships. The many side gets repeated but the one side should not.

What you are describing is a classic case of the one and many sides being reversed. In the situation you described, it is the ADDRESS side that is the ONE and the PERSON side that is the many. Or, structurally speaking, the address is the parent of the person. But from your stated symptom, you have it the other way around.

 

[enriquemellado]

thanks for the reply doc man
in my relationships window the AddressID has a 1 by the way this address is the PK for the Address table and is an autonumber. And the AddressID on the Person table has the many sign and is a number field and not a PK.

My Address table has duplicate addresses. with different AddressID numbers. so the related AddressID in the persons table has a different number in the field where 2 or more People have the SAME address and i think they should have the same one.

am I still doing something wrong?
thanks for the great help

 

[Fear Naught]

Can you post a copy of the relationship window?

 

[The_Doc_Man]

There is also a question about how you are populating the tables.

If you do this directly through a table window, you are trusting Access to do the right thing but it won't.

If you have a form with parent/child relationship defined correctly and have a parent/child FORM to go with the parent/child table, and if you have the so-called "linking" field defined correctly, then the parent (address table) form can be used to put in addresses and the child (person table) form can be used to specify each person who lives at that address. The form will do the right thing if the relationship is right.