Record Update in Access

Can you make update query? Join ID fields in both tables, select fields for update from LIST002 and set update to fields from LIST 001

Not sure how to do that

Unless I have misunderstood you are describing one of the fundamental aims of a relational database such as Microsoft Access. i.e.- eradicate data replication.
you may wish to read more database normalisation theory here - http://en.wikipedia.org/wiki/Database_normalization.
in Microsoft Access it would be better to create LIST002 as a query deriving the fields you require from LIST002.
post back if I have misunderstood.

I have access 2010

These tables are in access but they are created daily from sharepoint and excel feeds.
 
I just need a simple macro or something that can do this.

Check example
db5.accdb

looks cool but it gets an error, and I can't see what it is doing

What error your get?

The expression On Click you entered as the event property setting produced the following error: a problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX Control.

Please, check this example. If it will not work again, import all (tables, form and query) in clear DB, open Form1 and press button
Database4.zip

nope not working, I just need a simple way to do this.

use this string:
DoCmd.RunSQL ("UPDATE LIST001 INNER JOIN LIST002 ON LIST001.ID = LIST002.ID SET LIST001.Phase = [LIST002]![Phase], LIST001.Description = [LIST002]![Description];")

Query is in picture
query.png

so do i do with command or screens, not a huge access guy

Check again.
db5.zip

download and tried to run, no error but it does not update.

My access db name is Opportinity_Feeder

Table1 - Opportunities
Table2 - Opportunities_SolutionTrack

Opportunities
- Opportunity Identifier
- Opportunity Name
- Opportunity Description

Opportunities_SolutionTrack
- Opportunity ID
- Opportunity Name
- Opportunity Description
- field 4
- field 5
- field 6
- firld 7

I need a macro or something that will update the common variables in Opportunities_SolutionTrack with like fields from Opportunities when Opportunities.Opportunity IDentifier = Opportunities_SOlutionTrack.Opportunity ID

with what I posted above would this work?

DoCmd.RunSQL ("UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.Opportunity Id = Opportunities.Opportunity Identifier SET Opportunities_SolutionTrack.Sales Stage = [Opportunities]![Current Sales Stage Code]")

And how do I run it?

Your need form (if you want to automate process.
Import Form1 from my example to your DB. Press Button.
db5.zip

Sorry, phrase not finished:
(if you want to automate process - you can use macro)

My question is will the code below do what I am asking for:

DoCmd.RunSQL ("UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.Opportunity Id = Opportunities.Opportunity Identifier SET Opportunities_SolutionTrack.Sales Stage = [Opportunities]![Current Sales Stage Code]")

If so how do I execute it?

You can execute code from any event on form (button pressed, for example) or from macros. This event is made for button on a form in my example

So in my database I can create a form like this with a button and right click the button under properties ONCLICK modify the event procedure to be

DoCmd.RunSQL ("UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.Opportunity Id = Opportunities.Opportunity Identifier SET Opportunities_SolutionTrack.Sales Stage = [Opportunities]![Current Sales Stage Code]")

Yes

You can also add before this string:
docmd.setwarnings False
and after:
docmd.setwarnings true

Then you will not get message ... records updated (OK, Cancel)

Now is there an easy way to test it first  

Change one string in source table and compare tables after update

Okay so when I build the form for onclick do I use
 
Macro Builder
Expression Builder
or
Code Builder

to put

DoCmd.RunSQL ("UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.Opportunity Id = Opportunities.Opportunity Identifier SET Opportunities_SolutionTrack.Sales Stage = [Opportunities]![Current Sales Stage Code]")

in?

Code Builder

I get a compile error when I click it

Here is what the code looks like

Private Sub Command0_Click()
DoCmd.RunSQL ("UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.Opportunity Id = Opportunities.Opportunity Identifier SET Opportunities_SolutionTrack.Sales Stage = [Opportunities]![Current Sales Stage Code]")
End Sub

All code should be in one string

here is the deck
testdb.accdb

For beginning I recommend you to use queries (you will not have errors with field names)
When query will do all what you need, you can use runSQL
testdb.zip

maybe I am missing something but it is doing nothing when I click the button

It is not asked you "Records will be updated?....."
Have you enabled content? (Yellow ribbon under standard ribbon in Access 2010)

that was the issue!
 
1. So now if I want to copy these pieces into my production DB how would I do that?
2. What do i modify to add additional fields to be updated?

Thanks

1. Import form and Query to you DB with import master (External data on ribbon). Open query in design mode and add all necessary fields, save, test and use

when I import the data the qry shows up in two places, just modify in one right?

It is by design of Access 2010. Select on top of navigation pane "Object Type"

so will this format be correct for multiple fields in the qry

UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.[Opportunity ID] = Opportunities.[Opportunity  Identifier] SET Opportunities_SolutionTrack.[Sales Stage] = [Opportunities]![Current Sales Stage], Opportunities_SolutionTrack.[Opprtunity Name] = [Opportunities]![Opportunity Name], Opportunities_SolutionTrack.[Opportunity Description] = [Opportunities]![Opportunity Description], Opportunities_SolutionTrack.[TCV] = [Opportunities]![Total Opportunity Value USD];

So I updated my production Access DB with the qry listed above, when I run I get the following:

First - You are about to run an update query that will modify data in your table "YES"
 
Second - You won't be able to undo the changes this action query is about to make to the data in a linked table or tables

The I get Run-Time error '3812':
You cannot update this field because the value your trying to apply is not valid or would break a data integrity rule

??????

Private Sub Command0_Click()

DoCmd.OpenQuery "Qry"
' OR
' docmd.runsql "UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.[Opportunity ID] = Opportunities.[Opportunity Identifier] SET Opportunities_SolutionTrack.[Sales Stage] = [Opportunities]![Current Sales Stage];"

End Sub

Should I be doing

Private Sub Command0_Click()

DoCmd.OpenQuery "Qry"
' OR
' docmd.runsql "UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.[Opportunity ID] = Opportunities.[Opportunity Identifier] SET Opportunities_SolutionTrack.[Sales Stage] = [Opportunities]![Current Sales Stage];"

End Sub
 
OR

Private Sub Command0_Click()

DoCmd.OpenQuery "Qry"
' OR
' docmd.runsql "UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.[Opportunity ID] = Opportunities.[Opportunity Identifier] SET Opportunities_SolutionTrack.[Sales Stage] = Opportunities.[Current Sales Stage];"

End Sub