Want To Update One Table Using Data From Another Table (1 Viewer)

kc27

New member
Local time
Today, 04:29
Joined
Mar 9, 2017
Messages
2
Hi

I am running Access 2013, 32 bit. I am looking to update one table, using data from another table.

The source table looks like this in Design View
Dept - Number
Class - Number
Vendor - Number
Style - Short Text
Style Desc - Short Text
ISN - Number (key)
Cost - Number
and many other fields

The destination table looks like this in Design View
Dept - Number (key)
Class - Number (key)
Vendor - Number (key)
Ele - Number (key)
P1501 - Number
P1502 - Number
and other P numbers

In these tables Vendors belong to Departments and a Class. One vendor may belong to multiple Departments. A realignment of Vendors to Departments took place and some Vendors switched from one Department to a different Department. Here is an example

Vendor Dept Class
154 256 124 (3 records because there are 3 different ISN numbers)
154 256 115 (1 record because there is just 1 ISN number)
154 256 125 (2 records because there are 2 different ISN numbers)
154 263 117 (9 records because there are 9 different ISN numbers)
154 270 370 (2 records because there are 2 different ISN numbers)

The Source table has the updated correct Vendor - Department -Class records.

I need to get the Destination table so that it also has same Vendor - Department - Class

I created an Update Query with the Source and Destination Tables. I did a join on the Class and Vendor fields. At the bottom of the query window, I had this:

Field: Dept
Table: Destination Table
Update to: SourceTable.Dept
Criteria:
Or:

I am thinking this will update the Destination table with the correct Vendor - Dept. - Class. Not sure if there is something I should be entering for criteria so that if the Vendor - Dept - Class are the same, it can be skipped.

Also when comparing the two tables: I can see that some records will need to be added to the Destination table, and some records will need to be deleted from the Destination table, in order to get it cleaned up and in sync with the Source table.

Any advice on how to accomplish the above tasks would be appreciated. Thanks in advance for your time.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:29
Joined
Jul 9, 2003
Messages
16,243
I think you need a separate table for the "P" numbers...

"Many Other Fields" indicates a possible design issue.
 

kc27

New member
Local time
Today, 04:29
Joined
Mar 9, 2017
Messages
2
Thanks for the suggestion. I am not the architect of the database, nor am I qualified to make structural changes. I can tell you that these are not new tables, they have been in place for several years as configured, and have worked fine. The architect/database administrator is no longer available to manage upkeep such as what I described, that was was my reason for writing. I was trying to maintenance the tables to keep them current. I only used the phrase "many other fields" to eliminate the need to write out every field in the tables.
 

Users who are viewing this thread

Top Bottom