asked on

Microsoft Access Query To Combine Text

Hello.
I need to use a query to combine text strings from a table based on an order id, and a line number sequence. The line sequence will always be in order, but will not start 1, 2, 3..... The first string is independent of the others. The remaining strings need to be combined into one memo field. Here is a sample of the data.

Ord_No      Cmt_Seq_No      Cmt

00332514      3      Happy Anniversary
00332514      4      Brit and Herb, For your Anniversary. We
00332514      5      hope you get to use this frequently. Now
00332514      6       come visit your ill mother. Love Mom an
00332514      7      d Dad
00332515      9      Merry Christmas
00332515      10      Merry Christmas Michael, for all your tr
00332515      11      avels. love MOM

Is this possible?>
Thank you!

Jeffrey Coachman

see this link:
https://www.experts-exchange.com/questions/28264031/Append-several-records-to-one-memo-field-using-VBA.html

Jeffrey Coachman

Note that in your text, some words are continued on the next record, so it will be tricky to know when it breaks in the middle of a word... (to insert the space-bar delimiter or not)

...so these odd cases will probably have to be handled manually

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chantalcookware

ASKER

Having trouble opening the mdb's. Can you post query logic to add to a new table memo field?

Jeffrey Coachman

<Having trouble opening the mdb's. >
Please clarify...

<Can you post query logic to add to a new table memo field? >
That info (and the query used/needed) is in the link I posted, ...it is a simple INSERT query...

chantalcookware

ASKER

Tells me file is untrusted. I will look around for workaround to open it.

chantalcookware

ASKER

I got it open and test db works. Can the ord_no field be text? I can't make it work with the text ord_no field.
Thank you again.

Jeffrey Coachman

Lets back up...

Did the sample I posted work for you?
Yes or No?

chantalcookware

ASKER

Yes, it works with an additional autonumber field, and a numeric order number field. Thank you.

Jeffrey Coachman

Change the query to this:

SELECT YourTable.Ord_No, DConCat("cmt","YourTable","Ord_No = " & "'" & [Ord_No] & "'"," "," ",False) AS Combined
FROM YourTable
GROUP BY YourTable.Ord_No, DConCat("cmt","YourTable","Ord_No = " & "'" & [Ord_No] & "'"," "," ",False);

chantalcookware

ASKER

Thanks. I was able to do some other tweaks and make your original code work.

Jeffrey Coachman

great

;-)