How do I update fields in one table from another table

[Randoz]

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.

[cori25]

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

[jaxjagfan]

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.

[Randoz]

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.

[cori25]

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!

[Scott Price]

A question that hasn't been asked yet, but is quite pertinent: Why do you have two identical tables?

Regards,
Scott

[Randoz]

I copied the tblPersAdd tabe from tblPers table. I need a master table so the user can't mess up the master.

[Scott Price]

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

[Randoz]

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.