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.
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.