How To Use One Table To Update Another Table?

[kc27]

In an Access database, I have a linked table that includes fields Dept, Class, and Vendor, plus other fields. This would be my source table



That Access database also has an Access table that includes Dept, Class, and Vendor. This would be my destination table

In the course of business, some Vendors moved to different Departments, for example Vendor 12, used to be in Dept. 22, now it is in Dept 32. The source linked table is accurate, the destination Access table is not.

How does one take a known good table and update a second table so that the two are in sync? Somehow I need to take the source database, see what departments the vendors belong to, and also what their class number is for those vendors, then update the Access table so it is in sync and so that vendors belong to the same department and have the same class number as the source table.

I tried an update query, it changed data in the destination table, but it did not update correctly. Thanks in advance for any ideas on this.

[ranman256]

join the 2 tables in a query, update the table.field you want with the table.field from the other table.
join on the common fields.

[kc27]

Thanks, I will give that a shot. There are instances where a vendor is in one table but not in the other. See screen shot below for an example. Vendor 1084 is partially in the destination table. Will this type of query get the two tables in sync?

[ranman256]

if it exists, it will update.

[kc27]

If I link all three fields, the destination table does not update. If I only link vendor, the destination table updates, but some old data remains. Will an update query remove data from the destination table that exists in the destination table but does not existt in the source table?

[aytee111]

One of the first rules of database normalization is that data is NEVER repeated - this problem you are having is exactly the reason why it should not be done. If you require it then a number of queries need to be created.

1 - join the two tables by vendor and class, update the dept on the second table if it is different
2 - create a unmatching query on the three fields and append the missing vendors to the second table - see here how to do that

Now you are left with orphan records in the second table, records that don't exist in the source. Create another unmatching query and delete those records.

[kc27]

aytee111 - thanks for the additional information on this. The creator and administrator of this database who passed it along had alluded that updating this table was a multi-step process. I will follow thorough on the new information you have provided and see how it goes.

[kc27]

I must not be doing this portion of step 1 correctly "update the dept on the second table if it is different" Screen shot below. Do I have the query wrong? I currently have it as a select query vs an update query just to see what data comes back.

[aytee111]

You don't say what isn't working with this query?

[kc27]

Sorry for the lack of specifics

I run the query and get a message that states: "You are about to update 212490 rows", I click yes and get this message. I do click yes, but the message seems to state that 14,610 records will not be updated. Is this something I can ignore, or do I need to modify the query?

[aytee111]

I think it means that those 14610 records already exist, for instance vendor 1082 class 828 you are trying to update the dept to 78 but that record already exists. Why is Dept part of the primary key? In the screen print you posted (post #3) it looks like Vendor and Class are sufficient to make the record unique, is this how the records are? The error message seems to say differently.

[kc27]

The graphic in post #3 was generated from a program called Beyond Compare. I exported the source and destination tables to Excel, then compared them using Beyond Compare. In the Beyond Compare interface, you can select key fields. I selected vendor because it was vendors that had moved to different departments. Below is a Design View of the two tables showing their keys.

[kc27]

I meant to ask this earlier. Where you stated in post #6

1 - join the two tables by vendor and class, update the dept on the second table if it is different

How do I control the update of the destination table so that it occurs only "if it is different"?

[aytee111]

I asked some questions in post #11 - ?

[kc27]

I think it means that those 14610 records already exist, for instance vendor 1082 class 828 you are trying to update the dept to 78 but that record already exists. Why is Dept part of the primary key? In the screen print you posted (post #3) it looks like Vendor and Class are sufficient to make the record unique, is this how the records are? The error message seems to say differently.

Thanks for your help. I am new to database maintenance, and to Access. I do not know why the table was set up with Dept is part of the primary key. Here is some additional information that I hope will help sort this out.

1. A vendor can belong to more than one department
2. Departments utilize class with each vendor

Vendor and Class alone do not make the record unique. Below are some examples:

Here is the source table filtered to show Vendor 1082.
http://www.clemke.com/misc/vendor1082rpstyles.gif

Here is the destination table filtered to show Vendor 1082 prior to any updates
http://www.clemke.com/misc/vendor1082webdcvweek.gif

Here is an example of what I mean by a vendor can belong to multiple departments
http://www.clemke.com/misc/vendor927...epartments.gif