person link to adres, howto?

[Dinus]

Hello all,

I'm new here so hello everybody.

Ok, I'm pretty new to Access '97 and so I have a problem already.
I want to make an adres database. I want to make the adres central and link the persons to that adres, so that i can have multiple persons on the same adres. I created an adres table and a person table, but how do I link the person to the adres???

 

[ColinEssex]

Its a one-to-many link. One address can have many persons.

If you create a field in the Address table called "AddressID" and set it as an AutoNumber. You then have a foreign key in the persons table called "AddressID" set as a number

In the relationships window, link the 2 together on the "AddressID" field as a one-to-many link.

Create the address form and have a subform to add the persons who have that address. Create the subform with the wizard and because you will have done the relationships already it will automatically link so you only get the people with the same AddressID as the Addresses.

Then when its done, you can select an address and the subform will list the relevant persons.

Col

 

[Dinus]

Ok, that makes a little sense to me

Now I have linked the 2 AddressID's but when i put a new entry in the Querie he makes a new row in the person table (i want that) but also in the address table (i dont want this). I dont want to have another line in Address becose when someone moves, i only have to change 1 line in the table. I didnt find the one-to-many link btw.

 

[ColinEssex]

You are inputting data via a form and subform I hope
Col

 

[Dinus]

I don't even know how to start with a subform

 

[ScottGem]

Take a look at the Northwinds sample database for how to use Subforms. I would have the main form bound to the address and a subform bound to people linked on AddressID. When you assign or input someone in the people table using the subform the AddressID will automatically be filled in the people table.

 

[Dinus]

Tnx guys, it worked. Now the rest, i'm sure that you find some more questions from me on the forum soon