asked on

Update a table with another table data

I need an MS Access formula like this:

if the account on both tables are the same then update the name column with the name from table 2.

Table 1                  Table 2

Account      Name            Account      Name

0000012                  0000012      Office
0000013                  0000013      Meetings
0000014                  0000014      Travel
0000015                  0000015      Telephone
0000016                  0000016      Events

Thanks

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

Alternatively:

UPDATE Table1 INNER JOIN Table2 ON Table1.Account = Table2.Account
SET Table1.[Name] = Table2.[Name]

Jesus Rodriguez

that's another method

update table 2
set table2.field=table1.field
inner join table1
on table1.field=table2.field

Kdankwah

ASKER

Unfortunately, I have to update the same table with a sort order field that I created on table 1. This is what I have now.

UPDATE July_August INNER JOIN Accounts_table ON July_August.Account = Accounts_table.Account SET July_August.OAD_Category = Accounts_table.OAD_Category;

Its working perfectly. Can I add another field to be updated called Sort_Order.

I did not want to open up another ticket.

Thanks

mbizup

Like this?

UPDATE 
July_August INNER JOIN Accounts_table ON July_August.Account = Accounts_table.Account 
SET 
July_August.OAD_Category = Accounts_table.OAD_Category,
July_August.Sort_Order= Accounts_table.Sort_Order

Open in new window

<<
I did not want to open up another ticket.
>>

As a general rule you really should open seperate tickets rather than posting to closed questions. It makes your new question visible to more people - which can result in a faster answer since the original participants might not be online, and it keeps the original question focused.

Kdankwah

ASKER

Thanks