Please Help with Adding to field????

[Nyrubi]

Hi I have an Access table that I will need to update with data from an Excel
sheet. There is a field (comments) in the Access table that already has data
in it. Therefore, I need to update field (comments) and add data from the
Excel sheet. I do not want to override what is already in Access I want to
add to the comments field from Excel. Can this be done? If so, how do I
write my query? If you can provide an example that would help very much.
Thank you.

 

[Michel Walsh]

I assume you already have you Excel data into a "table", say tableB. I also
assume you want to append to a text field, TableA.F1 so that after the
update, you have what was in that field, a space, and the data in tableB.F1.
I also assume there is a field "unitID" , common to both tables, which
associate the comment in field f1 to some identifiable something.


UPDATE table1 INNER JOIN table2
ON table1.unitID = table2.unitID
SET
table1.F1 = table1.f1 & " " & table2.f1



Note that if the id values does not already appear in table1, no new record
would be added. We can correct that, if you want, with:


UPDATE table1 RIGHT JOIN table2
ON table1.unitID= table2.unitID
SET
table1.F1 = table1.f1 & " " & table2.f1,
table1.unitID= table2.unitID




Also note that the updated field f1 is still limited to the maximum number
of characters specified in the table design for that field (and an absolute
maximum of 255 characters).



Hoping it may help,
Vanderghast, Access MVP

 

[Ron2006]

Create update query with ImportedDataTable linked to ExistingDataTable
via whatever are the matching criteria items.


Select the ExistingDataTable.Coments field

Update to:

ExistingDataTable.Comments & " //// " & ImportedDataTable

obviously backup your table first until you have all the bugs out of
your update query.


1) This type of update means the same imput should NOT be run more
than once for the same data.

2) I added the & " //// " as a mechanism by which you can tell the
difference between pre-existing information and the update. You may
even want to add other information. Maybe something like: & " ////
" & Date() & " //// "

Obviously that is your call.

Ron