Help with update query

[china99boy]

Hi guys...I can't figure this out after trying for several hours.

I have table A with fields "date" "AgentID" and "Msg"
Table B with has several fields, which also includes "Msg" "date" "AgentID" among others.

I am trying to add the data from table A "Msg" to the matching record in table B and just replace "Msg" data. AgentID is the key field.

If a matching record is not found in table B based on AgentID and date then a new record should be added.

Any help will be greatly appreciated.

 

[CJ_London]

you need two queries, the first to do the update and the second to do the insert.

In both cases the query uses tableA and tableB. The following is the principle but for it to work you will almost certainly need additional criteria - the below will only work if there is no more than one record per agent in either tables. If there is more than one record you need to extend the criteria to identify the match precisely.

To do the insert, create an append query and assuming the common field between the two is AgentID, make a left join from AgentID in TableA to AgentID in TableB (make a link, double click on the link and select option 2 - include all records from tableA....). Then drag AgentID from TableB down as a field and set its criteria as Is Null - ensure the appendto value is left blank.

This will then only select records from tableA where there is not an existing record in tableB.

To do the update, create an update query and create an inner joinmake from AgentID in TableA to AgentID in TableB. This will only select records where an agentID already exists

 

[china99boy]

Thanks...I got it with your detail explanation.