Update query result to table

[hoachen]

I can't find a good example on the web showing how to update table with the
result from query. Table1 name "Agent1" column have: phone#(primary key),
birthdate, and agent (total record 100). table2 name "Agent 2" have the same
field (total record 150) except the data on agent is different.

I run a query for this two tables and want to update the table1 with the
table2 data where the phone#(primary Key). I want the result form the query
will replace the agent in Agent1 table.

How can i do that?

 

[John Spencer]

UPDATE Table1 INNER JOIN Table2
ON Table1.[Phone#] = Table2.[Phone#]
SET Table1.Agent = Table2.Agent
WHERE Table2.Agent is Not Null

In design view
-- Add both tables to the query
-- Join on Phone# (Drag from phone# to Phone#)
-- Select Table1.Agent field into the grid
-- Select Table2.Agent field into the grid
-- Set Criteria under Table2.Agent to Is Not Null (if you want to avoid
setting table1.agent to blank if the matching record in table2 has a blank
agent)
-- Select Query: Update from the menu
-- Enter [Table2].[Agent] into the UPDATE To under Table1.Agent (Be sure to
include the square brackets - if you don't the update will put the words
"Table2.Agent" into the field)
-- Select Query: Run from the menu

It would probably be a good idea to make a backup of your data before you
try this. The changes cannot be undone- so if something goes wrong you will
need to use the backup to restore the data.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[hoachen]

Thank you for the help. This will only show only match the data in table1. I
would like replace all the table2 data into table one data where the phone#
is match

UPDATE Table1 INNER JOIN Table2
ON Table1.[Phone#] = Table2.[Phone#]
SET Table1.Agent = Table2.Agent
WHERE Table2.Agent is Not Null

In design view
-- Add both tables to the query
-- Join on Phone# (Drag from phone# to Phone#)
-- Select Table1.Agent field into the grid
-- Select Table2.Agent field into the grid
-- Set Criteria under Table2.Agent to Is Not Null (if you want to avoid
setting table1.agent to blank if the matching record in table2 has a blank
agent)
-- Select Query: Update from the menu
-- Enter [Table2].[Agent] into the UPDATE To under Table1.Agent (Be sure to
include the square brackets - if you don't the update will put the words
"Table2.Agent" into the field)
-- Select Query: Run from the menu

It would probably be a good idea to make a backup of your data before you
try this. The changes cannot be undone- so if something goes wrong you will
need to use the backup to restore the data.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I can't find a good example on the web showing how to update table with the
result from query. Table1 name "Agent1" column have: phone#(primary key),
birthdate, and agent (total record 100). table2 name "Agent 2" have the
same
field (total record 150) except the data on agent is different.

I run a query for this two tables and want to update the table1 with the
table2 data where the phone#(primary Key). I want the result form the
query
will replace the agent in Agent1 table.

How can i do that?

 

[John Spencer]

I do not understand what you want. What I posted should replace the
Agent in Table1 with the Agent in Table 2. That is what you said you
wanted - or at least that is what I understood.

Do you want to
-- completely replace the matching records in table1 with those that are
a match in table2
Or
-- Add all the table 2 records to table 1 and get rid of the matching
records that were in table 1
or
-- Update more than just the Agent field?

You could use a delete and insert query if you want to completely remove
the records that match and insert ALL the records from Table 2

-- Delete all the matching records in table1
DELETE FROM Table1
WHERE Table1.[Phone#] IN
(SELECT Table2.[Phone#]
FROM Table2)

-- Insert (append) all the records from table two
INSERT INTO Table1
SELECT * FROM Table2



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you for the help. This will only show only match the data in table1. I
would like replace all the table2 data into table one data where the phone#
is match

UPDATE Table1 INNER JOIN Table2
ON Table1.[Phone#] = Table2.[Phone#]
SET Table1.Agent = Table2.Agent
WHERE Table2.Agent is Not Null

In design view
-- Add both tables to the query
-- Join on Phone# (Drag from phone# to Phone#)
-- Select Table1.Agent field into the grid
-- Select Table2.Agent field into the grid
-- Set Criteria under Table2.Agent to Is Not Null (if you want to avoid
setting table1.agent to blank if the matching record in table2 has a blank
agent)
-- Select Query: Update from the menu
-- Enter [Table2].[Agent] into the UPDATE To under Table1.Agent (Be sure to
include the square brackets - if you don't the update will put the words
"Table2.Agent" into the field)
-- Select Query: Run from the menu

It would probably be a good idea to make a backup of your data before you
try this. The changes cannot be undone- so if something goes wrong you will
need to use the backup to restore the data.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I can't find a good example on the web showing how to update table with the
result from query. Table1 name "Agent1" column have: phone#(primary key),
birthdate, and agent (total record 100). table2 name "Agent 2" have the
same
field (total record 150) except the data on agent is different.

I run a query for this two tables and want to update the table1 with the
table2 data where the phone#(primary Key). I want the result form the
query
will replace the agent in Agent1 table.

How can i do that?