geeta_m9
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
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
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.
ASKER
It is not a memo field but Longtext. I am using Access 2013.
ASKER
Hi Rey, I keep obtaining a syntax error when I try to execute the SQL.
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];
UPDATE Alumni INNER JOIN Alumni AS Alumni_1 ON Alumni.ID = Alumni_1.ID
SET Alumni.Comments1 = [Alumni].[Comments1] & " " & [Alumni_1].[Comments2];
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your assistance.
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]