By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

update Address ID field in Person Table with Address Table

P: 2
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
Apr 1 '10 #1
Share this Question
Share on Google+
3 Replies


patjones
Expert 100+
P: 931
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
Apr 2 '10 #2

P: 2
Pat,

thankyou so much ,

can this be done using triggers in sql ?

s
Apr 2 '10 #3

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblMain.[person id], tblMain.[last name], tblMain.[first name], tblMain.[address id] 
  2. INTO tblPersons
  3. FROM tblMain
  4.  

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:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblMain.[address id], tblMain.[address name], tblMain.[line 1], tblMain.[line 2], tblMain.[city], tblMain.[state], tblMain.[country] 
  2. INTO tblAddresses
  3. FROM tblMain
  4.  

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
Apr 3 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.