asked on

Appending values from one column in a table to another column within the same table

I have a table in MS-Access called Alumni that has two columns with comments in them, i.e., Comment1 and Comment2. I would like to append the values from the Comment2 column into Comment1 so that I can then get rid of the Comment2 column.

How can I accomplish something like this? Can you show me both in terms of query design view and SQL? I have attached a screen shot of the query design.

Thanks.
Comments_Update.jpg

Rey Obrero (Capricorn1)

Use an Update Query

In SQL view

Update Alumni
Inner Join Alumni_1 On Alumni.[IDFIELD] = Alumni_1.[IDFIELD]
set Alumni.Comments1= [Alumni].[Comments1] & " " & [Alumni-1].[Comments2]

in design view

Field Comments1
Table Alumni
Update to [Alumni].[Comments1] & " " & [Alumni-1].[Comments2]

typetoit

I assume that comment1 and comment2 are memos. I am not sure which version of access you are using but in my experience memos can only hold up to 256. If you are putting both of these comments into one field, you will need to check the length of the fields.

geeta_m9

ASKER

It is not a memo field but Longtext. I am using Access 2013.

geeta_m9

ASKER

Hi Rey, I keep obtaining a syntax error when I try to execute the SQL.

geeta_m9

ASKER

I finally did get it to work, but I had to modify the query to as follows:

UPDATE Alumni INNER JOIN Alumni AS Alumni_1 ON Alumni.ID = Alumni_1.ID
SET Alumni.Comments1 = [Alumni].[Comments1] & " " & [Alumni_1].[Comments2];

geeta_m9

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for geeta_m9's comment #a41044170
Assisted answer: 500 points for capricorn1's comment #a41044024

for the following reason:

I listed my comment as the solution because it is the most accurate answer syntactically.

geeta_m9

ASKER

Rey - thank you for your help. I had to make a modification to the syntax in order to get the query to run.

SOLUTION