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

How do I update fields in one table from another table

P: 27
I have two identical tables with all the same field headers. One is my master "tblPers" and the other is a user input one "tblPersAdd". How do I incorprate the changes from the user one to my master? I have tried the append query and the update query to no avail. Maybe I am doing something wrong. The name is the one unique field between them, it will never change. Thanks for the help.
Mar 12 '08 #1
Share this Question
Share on Google+
8 Replies


cori25
P: 83
Hello,

You would want to append the data from tblPersAdd to tblPers.

1. Create a new query and bring in the tblpersadd.
2. Bring in all the fields from tblpersadd
3. Change the query to an append query, it will ask which table you would like to append to, in the drop down select tblpers
4. Once you have done this be sure that under each field you are appending that you fill in the "Append To" field. This simply will drop down what field you have in tblpers. Therefore, if you have the Name field in tblpersadd, it would be appending to the Name field in tblpers.
5. Save the query and run it
Mar 12 '08 #2

jaxjagfan
Expert 100+
P: 254
I have two identical tables with all the same field headers. One is my master "tblPers" and the other is a user input one "tblPersAdd". How do I incorprate the changes from the user one to my master? I have tried the append query and the update query to no avail. Maybe I am doing something wrong. The name is the one unique field between them, it will never change. Thanks for the help.
There could be 2 events that occur here. An update and an add.

First step is an update query which will update tblPers so that any data changes in tblPersAdd will be reflected in tblPers. Make an update query and link the name fields. Include all the fields or only the ones the end user can update.

If the name field is set to be unique (no duplicates) then the 2nd step is to append tblPersAdd to tblPers. This will add new records to the master. With the name field set for "no duplicates" only new records will append to tblPers.
Mar 12 '08 #3

P: 27
I created an append query and was able to add a new record with out a problem. However, I was not able to update certain fields on the main table with the append query or update query. When I created the Update Query and ran it, I kept getting a window stating that the data base didn't updated 0 fields due to a type conversion failure, 1 record due to key violations, 0 records due to lock violations and 0 records due to validation rule violations. As of right now, I only have one record in the tblPersAdd table to test the procedure.
Mar 13 '08 #4

cori25
P: 83
This error sounds like it is because the two tables do not have the same value(text, number, etc...). In order to append from one table to another they must be the same. Therefore, check the tables in design view and ensure they are matching.

Let me know what happens.

Thanks!
Mar 13 '08 #5

Scott Price
Expert 100+
P: 1,384
A question that hasn't been asked yet, but is quite pertinent: Why do you have two identical tables?

Regards,
Scott
Mar 13 '08 #6

P: 27
I copied the tblPersAdd tabe from tblPers table. I need a master table so the user can't mess up the master.
Mar 19 '08 #7

Scott Price
Expert 100+
P: 1,384
What is the purpose of this isolation? Is it to reduce user entry errors, or to restrict user ability to view certain records?

Regards,
Scott
Mar 19 '08 #8

P: 27
I get data via a spread sheet with employee information. I up load this info into a data base. Occasionaly I get future spread sheets with new employees and updated current employee information. I want to be able to bring in the new and updated info. The table in the data base and the spread sheet fields always equal each other.
Apr 1 '08 #9

Post your reply

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