update Address ID field in Person Table with Address Table

[sreene9]

I'm new to this forum , and ms access too,

I have two tables :

address table(address ID, addressname, line 1 , line 2 , city , state country)
and
person table(person id , last name, firstname, addressID)

In order to link the two tables , i need to update the address id field in person table once the address table is updated with all the available addresses ? if yes , that should be done manually ! i have over 3000 records..

could someone suggest a detailed method of how this can be done in a much easier way.

thankyou for reading ,

Sreenu

[patjones]

sreene -

The way things stand now, if you don't have any address ID's filled in the "person" table, you would in fact have to manually figure out which address belongs to which person and enter the appropriate address ID in that person's record. Very time consuming.

If you have a single table somewhere with the person and address combined, the best thing to do would be to assign an address ID to each person in that table however you see fit (it makes the most sense to use something derived from real life, such as customer number or something like that). Then you could split the table into a person and address table, retaining the address ID in each one.

I hope this makes sense.

Pat

[sreene9]

Pat,

thankyou so much ,

can this be done using triggers in sql ?

s

[patjones]

sreene -

My knowledge of SQL is not advanced enough to advise you on the use of triggers. Here's what you can do though.

Suppose you have tblMain, with the combined information for "person" and "address":

tblMain (person id , last name, first name, address id, address name, line 1, line 2, city, state, country)

Using SQL in Access Query Design view, you can create the "person" table using:

SELECT tblMain.[person id], tblMain.[last name], tblMain.[first name], tblMain.[address id] 
INTO tblPersons
FROM tblMain
 

This will create a new table and put the data in it. You might need to then open the table in design view to fine tune the column parameters, set keys, and so forth. You could do the same procedure to generate the address table:

SELECT tblMain.[address id], tblMain.[address name], tblMain.[line 1], tblMain.[line 2], tblMain.[city], tblMain.[state], tblMain.[country] 
INTO tblAddresses
FROM tblMain
 

I personally think the ID structure you are using could be setup a little differently; the way it is now, you will either have a one-to-one relationship between a person and their address, or worse be storing a person more than once in the person table, with a different address ID each time, should you want to store more than one address for a person. By modifying the key structure a bit you can increase the flexibility of the database and maintain a normalized table structure.

Pat