Solved

Access update one tbl with contents from anohter tbl

Posted on 2014-02-28
6
280 Views
Last Modified: 2014-02-28
I have a table called "Source". The source table has three fields called A, B, and C. Fields A and B are primary keys. Field C has amounts that I want to copy to my "target" table based on matching the two primary keys. The target table also has fields A, B, and C. How do I do this?
conernesto
0
Comment
Question by:Conernesto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39895915
Try running an update query:

UPDATE Source, Target
SET Target.c = Source.c
WHERE Source.a = Target.a AND source.B = Target.b

Open in new window

0
 

Author Comment

by:Conernesto
ID: 39895962
I am not sure how to do this.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39895999
1.  Open a new query
2.  Close the "Show Table" dialog without selecting any tables.
3.  Right click the query window and select "SQL View"
4.  Copy/paste the SQL I gave you into the SQL Window
5.  Try running it by clicking the Exclamation Point above the query window OR by right-clicking the query window and selecting Datasheet View.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Conernesto
ID: 39896162
It worked. How can I put the above code in a command button?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39896269
You can embed it in VBA in the click event of your command button like this:

Sub YourCommandButtonName_Click()
       Dim strSQL as string
       strSQL = "UPDATE Source, Target SET Target.c = Source.c WHERE Source.a = Target.a AND source.B = Target.b"
      CurrentDB.Execute strSQL, dbFailOnError
End Sub

Open in new window

0
 

Author Closing Comment

by:Conernesto
ID: 39896275
Thank you.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question