Solved

Access update one tbl with contents from anohter tbl

Posted on 2014-02-28
6
282 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

624 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