Results 1 to 3 of 3
  1. #1
    aero is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    12

    concatenate text column by grouping?

    Hello,
    Our tables store our notes in sequential observations parsed 50 characters per row such that the note gets broken into n records with the same date_time and time and are labeled sequentially. What I'd like to do is query to put these back together. I am aware of doing a crosstab query and then concatenating them, but these notes can exceed the number of columns a cross tab query is limited to. Does anyone know how to do this? I've done this before in SAS by creating a new column and then starting at the bottom sequence of each grouped (survey,ID,date_time) and concatenating the text line above to the one below. This may require VBA but I don't know VBA. Anyway,
    Please post sample code if you can, I'm still learning.

    Survey ID date_time Seq time_key Textx
    123 1 datetime1 1 time1 Hello
    123 1 datetime1 2 time1 World
    123 1 datetime2 1 time2 Good
    123 1 datetime2 2 time2 Bye


    Desired Result:
    Survey ID date_time Seq time_key Textx
    123 1 datetime1 whatever time1 Hello World


    123 1 datetime2 whatever time2 Good Bye

    Please Help!


  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    to do this purely in sql would be quite difficult, but can be done quite easily if you use the concatrelated function developed by allen browne - see this link here for the code

    http://www.allenbrowne.com/func-concat.html

    your sql would look something like

    SELECT DISTINCT SurveyID, datetime, concatrelated("textx","myTable","surveyed=" & surveyed & " and datetime=" & datetime,"seq"," ") as textz
    FROM myTable

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Does the Survey and ID change? Would your data be something like this? If not can you give an example.

    123 1 datetime1 1 time1 Hello
    123 1 datetime1 2 time1 World
    123 1 datetime2 1 time2 Good
    123 1 datetime2 2 time2 Bye

    124 2 datetime1 1 time1 I am

    124 2 datetime1 2 time1 Hungry

    125 3 datetime1 1 time2 The

    125 3 datetime1 2 time2 End



Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to bold text within concatenate ?
    By adnancanada in forum Queries
    Replies: 12
    Last Post: 02-10-2016, 03:04 AM
  2. Replies: 5
    Last Post: 04-07-2015, 02:20 PM
  3. Replies: 11
    Last Post: 12-09-2013, 06:33 PM
  4. Replies: 3
    Last Post: 12-07-2011, 03:02 PM
  5. Replies: 3
    Last Post: 10-16-2009, 09:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums