Update Query

[rockrider]

Hello everyone.

I have a question, I was stuck all day on this :s

I have two tables (tbl1 and tbl2) with some fields. Basically I need to update the Comments (Memo Field) of tbl2 using the same field of tbl1 without overwriting the current data on Comments field of tbl2. Also I would like to have one or two line breaks between the "old" data and the "new data".

Something like:

Comments:

02/03/2011 22:00 (WindowsUser):
new data

old data

How can I set this on the Criteria of my Query?

Any help would be much appreciated.

[maximus]

I have tried to replicate the problem in your post. This is What i have done:

assumptions:
1) Assumed that there is only one set of record for each Id in each of the table.

2) Code will run even if u have data for 1 ID in one table and not in the other. e.g for ID=4 in Table2 u have sam but there is no ID=4 in Table1 the code will still run.




1) I have two tables Table1 with two fields ID and comments.
2) Based on the Id Number I will update the data of Table1 (That will be old data) insert New data (From Table2 where ID are same) with two line breaks in the middle.


I have used two recordsets. rst is to Table2 I have taken the Id from this RecordSet to open the corresponding row in Table1 and update the data.


Set rst = CurrentDb.OpenRecordset("Table2")
Do While Not rst.EOF
Set rs = CurrentDb.OpenRecordset("Select * from Table1 Where ID=" & rst!ID)
If Not rs.EOF Then
strmycomments = "Old data:" & rs!Comments & Chr(13) & Chr(13) & " New data:" & rst!Comments
rs.Edit
rs!Comments = strmycomments
rs.update
rs.Close
Set rs = Nothing
Else
rs.Close
Set rs = Nothing
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing


The above written code is attached to a Command Button please refer to the attached example. Before clicking on the Command buttom on the startupform Form3 check the tables Table1 and Table2. Click the button and then check Table1 again.

[weekend00]

can we just use a update join query to do it?
something like:

update table1 inner join table2 on table1.id=table2.id set table2.comments=table2.comments & chr(13) & chr(10) & table1.comments

[maximus]

This is the easy way to do it as pointed by Weekend00:

UPDATE Table1 INNER JOIN Table2 ON [Table1].[ID]=[Table2].[ID] SET Table1.Comments = "Old Data: " & [Table1].[Comments] & Chr(13) & Chr(13) & "New Data: " & [table2].[Comments];