Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-08-2017, 07:31 PM   #1
kc27
Newly Registered User
 
Join Date: Mar 2017
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
kc27 is on a distinguished road
Want To Update One Table Using Data From Another Table

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.

kc27 is offline   Reply With Quote
Old 03-09-2017, 03:30 PM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,555
Thanks: 369
Thanked 784 Times in 749 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Want To Update One Table Using Data From Another Table

I think you need a separate table for the "P" numbers...

"Many Other Fields" indicates a possible design issue.
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 03-09-2017, 09:27 PM   #3
kc27
Newly Registered User
 
Join Date: Mar 2017
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
kc27 is on a distinguished road
Re: Want To Update One Table Using Data From Another Table

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.

kc27 is offline   Reply With Quote
Reply

Tags
append , delete , query , update

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to update data from one table to another table using form. access 2010 prabhus Forms 1 12-16-2013 05:45 PM
find data in one table and put in field of another table then update jom1918 Queries 1 11-07-2013 06:44 PM
how to update a filed of table A by entirng data on Table B armankln Tables 1 09-19-2012 07:23 AM
Update table 1 when data is entered into table 2 veraloopy Tables 2 12-15-2009 01:20 AM
[SOLVED] Update table with data from another table via form Robman40 Tables 1 12-02-2002 10:10 AM




All times are GMT -8. The time now is 07:05 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World