Solved

Updating Records in multiple tables

Posted on 2006-10-26
5
274 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:vibale
5 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17815668
create table relationship

set the referntial integrity
Cascade delete
cascade update
0
 

Author Comment

by:vibale
ID: 17815707
Yeah but the records arent exactly the same, one table has a couple of extra fields, will it still work?
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 18034771
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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now