Is there a way to merge columns (Excel question as well)

[BotCow]

I have a note field, a memo field, and a memo2 field that I need to merge together into one column. They're essentially the same thing (just notes). Is there a way to do this in access and/or excel?

 

[willir]

BotCow

It it were not for the fact you are using a memo field, the answer would be a qualified yes. Memo fields are more like objects, and are not really part of the table.

LittleSmudge is putting together a documentation FAQ on Memo fields.
thread700-651285 guess the one saving grace is that with Access XP / 2002, you can apparently treat the first 255 characters of a memo field as a text field. (I have not done this -- but it is docuemnted)

How to...

Append the strings using the "&" syntax...

strText = strText1 & " " & strText2

In this example, I inserted two spaces between Text1 and Text2.

Richard

 

[BotCow]

Oh hehe I'm sorry, let me clarify. They're text fields but the name of the field is "memo, memo2, and note". So it can be done?

 

[lespaul]

Yes since they are text files you can do this in a query:

SELECT MEMO + MEMO2 + NOTES AS NEWFIELD FROM TABLENAME

(the +'s may need to be & I can never remember which works where if I'm not actually doing it!)

Should work!

leslie

 

[BotCow]

What about physically merge the three columns into a new one in the table? So instead of having three columns queried to one, put them together into one universal column.

 

[BotCow]

Bump? Can't merge columns physically?

 

[willir]

BotCow

I tried posting yesterday but I kept timing out.

My question is regarding your expected outcomes.

Consider the following text...
text 1 "Jane loves apples"
text 2 "Jack love apples"
text 3 "Jim hates apples"

Are you trying to merge these three text fields together to get...
text 1 "Jane loves applesJack love applesJim hates apples"
??

Or
Do you want to see
"Jane loves apples"
"Jack love apples"
"Jim hates apples"

As three separate entries?

If it the latter, you may have to change your database design to include a one-to-many relationship.

I am going to use a survey example to demonstrate...

tblContact
ContactID
FName
LName

1 Jane
2 Jack
3 Jim

tblAnswer
AnswerID
ContactID
Answer

1 1 (Jane) "Loves apples"
2 1 (Jane) "Loves bananas"
3 2 (Jack) "Loves apples"
4 2 (Jack) "Hates bananas"
5 3 (Jim) "Hates apples"
6 3 (Jim) "Hates bananas"
7 3 (Jim) "Loves oranges"

Here each person can have two or more comments in the answer table.

Is this what you are looking for?

Richard

 

[AlanKW]

I'm reading the post as a one time instance of doing this and not something on the fly. (because he mentioned doing it in Excel as an alternative) So in which case you can do a make table of all the fields except the 3 text fields, combine the text fields and write that one. Or you could add a 4th text field in the table defination and do an update table to build it then just delete the other 3 columns.

 

[BotCow]

Well Willir, I don't really understand your example but I fixed it by manually merging the entries (it was only about 400 entries of copy and paste no big deal) but for future reference, in case I have 10,000 entries, here's what I was asking. Basically this is a document database that some how was created with three "Note" fields. The "Note" field is just to write down any extraneous comments or heads up in case the document is special, stored in another place, lost, etc.. I don't know why there's three note fields but only one is necessary, the rest are just junk. So I wanted to take all three fields and merge them as one. I won't get the problem you described above with strings bumping into each other (Jack loves applesJohn loves bananasSue likes dogs) because each record only has ONE "Note" field filled in. So basically with each entry, I'm trying to automatically eliminate the empty "Note" fields so essentially there will be one singular note field left for all records. This is really hard to explain but that's the best I could do. Don't worry about the response I guess, it's fixed and it didn't take too much time.

 

[GoDawgs]

AlanKW's response is correct for the future...just do a make-table query where you make all of the fields currently in the table except for the 3 note fields...then add another column like this:

Notes: [memo1] & [memo2] & [notes]

That's it...not too tough. Hope that helps.

Kevin

 

[willir]

BotCow

Sorry for the confusion.

From your example, you just wanted to append the Notes together.

The basic syntax, as provided in varations is...

Notes1 = Note1 & Note2 & Note3

One variation on a solution is to put the following code behind a command button.

[blue]
Dim dbs as DAO.database, rst as DAO.recordset
Dim strNote as String

set dbs = currentdb()
set rst = dbs.openrecordset("YourTableName&quot

rst.MoveFirst

Do until rst.EOF

strNote = ""

if not isnull(rst!Note1) then strNote = strNote & rst!Note1
if not isnull(rst!Note2) then strNote = strNote & rst!Note2
if not isnull(rst!Note3) then strNote = strNote & rst!Note3

rst.Edit
rst!Note1 = strNote
rst!Note2 = ""
rst!Note3 = ""
rst.Update

rst.MoveNext
loop

rst.Close
[/blue]


Substitute Note1, Note2 and Note3 for the names of your note fields in the loop. Substitute your table name where the record set is defined.