Updating Records in multiple tables

I have a form which enters a customers address details in a table. It then asks if you would like to add the address into another table ( job address table).

The problem I have is, is that these records can then be edited (individually). How can I make it so that if one of the addresses (multiple fields) is edited, it will automatically uupdate the same record in the other table?

Thanks
vibaleAsked:
Who is Participating?
 
Jeffrey CoachmanMIS LiasonCommented:
vibale,

Another way to look at this is to re-think your system for "Adding" the address to another table.

The rules of database design dictate that data should only be stored once. (you key in the address once and it only gets stored in that one place)

You can use a query to "display" the address fields in another table (so the Address data will always be the same in both places).

Here is a simple example:
Table Name:  tblEmployees
Fields:
EmployeeID (PK)
FirstName
LastName
Address
City
Region
PostalCode

Your table with the "extra" fields: tblEmployeeExtra
Fields:
EmpExtraID (PK)
EmployeeID (FK)
BloodPressure
BloodSugar

Create a query to join these two tables on "EmployeeID": qryEmployeeExtra

SELECT tblEmployees.EmployeeID, tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.Address, tblEmployees.City, tblEmployees.Region, tblEmployees.PostalCode, tblEmployeeExtra.BloodPressure, tblEmployeeExtra.BloodSugar
FROM tblEmployees LEFT JOIN tblEmployeeExtra ON tblEmployees.EmployeeID = tblEmployeeExtra.EmployeeID;

So if you change any address info in tblEmployees, it is reflected in this qryEmployeeExtra query.

When you need to update the "extra fields" simply create a main/subform (tblEmployees as the main and tblEmployeeExtra as the sub)

Hope this helps as well

Good luck

0
 
Rey Obrero (Capricorn1)Commented:
create table relationship

set the referntial integrity
Cascade delete
cascade update
0
 
vibaleAuthor Commented:
Yeah but the records arent exactly the same, one table has a couple of extra fields, will it still work?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.