Results 1 to 7 of 7

How do I concatenate text fields from multiple records

  1. #1
    Phil Hoop is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Location
    Edina, MN
    Posts
    9

    How do I concatenate text fields from multiple records


    I am working on a report that includes student comments on our various courses taught by various instructors on various dates and at various locations. I want to concatenate these comments and group them by Instructor, Course, Date, and Classroom. I have been googling the topic, but all the information that I've found seems to be oriented towards a single factor and not the 4 factors I'm working with. I found an article on "ConcatRelated', but couldn't get it to work after over an hour of trying different input attempts. Probably my fault, but the directions/explanations didn't give me enough information to succeed. I would appreciate any suggestions that you are willing to give me, I thank you in advance. ...............Phil Hoop

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,339
    Is this what you tried http://allenbrowne.com/func-concat.html

    This source is commonly referenced for the situation you describe.

    How did you implement the code? How do you call the function - in query or in textbox on report? It is simple enough to have multiple WHERE criteria in the function call. Are those 4 criteria all in the same table?
    Last edited by June7; 09-11-2017 at 10:46 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Phil Hoop is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Location
    Edina, MN
    Posts
    9
    Yes, I tried the allenbrown reference you suggested, but I couldn't get it to work. I obviously was doing something wrong, but after a couple of hours I threw in the towel. Can you help me with some clearer directions?

    After further thought, here is an illustration of what I'm seeking.

    First I have a query say query 1 that produces a record set that looks like this:



    Instructor Course Date Location Comment



    Joe Course ABC 1/1/2017 RM102 Comment 1

    Joe Course ABC 1/1/2017 RM102 Comment 2

    Joe Course ABC 1/1/2017 RM102 Comment 3

    Joe Course DEF 2/1/2017 RM304 Comment 4

    Sam Course ABC 3/1/2017 RM208 Comment 5

    Susan Course JKL 4/1/2017 RM311 Comment 6

    Susan Course JKL 4/1/2017 RM311 Comment 7

    Susan Course JKL 4/1/2017 RM311 Comment 8

    Susan Course MNO 5/1/2017 RM102 Comment 9

    Susan Course MNO 5/1/2017 RM102 Comment 10

    Susan Course MNO 5/1/2017 RM104 Comment 11



    Now I want to produce Query 2 that looks like this:



    Joe Course ABC 1/1/2017 RM102 Comment 1 & Comment 2 & Comment 3

    Joe Course DEF 2/1/2017 RM304 Comment 4

    Sam Course ABC 3/1/2017 RM304 Comment 5

    Susan Course JKL 4/1/2017 RM311 Comment 6 & Comment 7 & Comment 8

    Susan Course MNO 5/1/2017 RM102 Comment 9 & Comment 10

    Susan Course MNO 5/1/2017 RM104 Comment 11



    Do you have any suggestions? Thank you for any advice you are kind enough to offer.



    ...........................Phil Hoop

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,327
    Tell us about your tables --- post a jpg of your tables and relationships.

  5. #5
    Phil Hoop is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Location
    Edina, MN
    Posts
    9
    Thanks for your interest. I only have one table, and I will use a query against that single table like I posted above for this exercise. That table is populated by using AutoData to scan the results of a survey form we ask our students to complete at the end of a class. Here is another mockup of both the query that I am starting with that I created and a mockup of the query I want to create. I appreciate any suggestions that you can offer for transforming Query 1 into query 2. I have googled concatenation and found 4 or 5 functions that are offered as a possible tool, but I can't get any of them to work. Obviously I am doing something wrong, but the directions offered with these functions aren't clear enough for me as my failures can attest. So, perhaps I only need some help with using an existing tool.



    Instructor Course Date Location Comment



    Joe Course ABC 1/1/2017 RM102 Comment 1

    Joe Course ABC 1/1/2017 RM102 Comment 2

    Joe Course ABC 1/1/2017 RM102 Comment 3

    Joe Course DEF 2/1/2017 RM304 Comment 4

    Sam Course ABC 3/1/2017 RM208 Comment 5

    Susan Course JKL 4/1/2017 RM311 Comment 6

    Susan Course JKL 4/1/2017 RM311 Comment 7

    Susan Course JKL 4/1/2017 RM311 Comment 8

    Susan Course MNO 5/1/2017 RM102 Comment 9

    Susan Course MNO 5/1/2017 RM102 Comment 10

    Susan Course MNO 5/1/2017 RM104 Comment 11



    Now I want to produce Query 2 that looks like this:



    Joe Course ABC 1/1/2017 RM102 Comment 1 & Comment 2 & Comment 3

    Joe Course DEF 2/1/2017 RM304 Comment 4

    Sam Course ABC 3/1/2017 RM304 Comment 5

    Susan Course JKL 4/1/2017 RM311 Comment 6 & Comment 7 & Comment 8

    Susan Course MNO 5/1/2017 RM102 Comment 9 & Comment 10

    Susan Course MNO 5/1/2017 RM104 Comment 11



    Do you have any suggestions? Thank you for any advice you are kind enough to offer.


    ...........................Phil Hoop

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,339
    The 'existing tool' is VBA. The referenced custom function is the means to accomplish what you want.

    Example of using the function in a query:

    SELECT Instructor, Course, [Date], Location, ConcatRelated("Comment", "Table1", "Instructor='" & [Instructor] & "' AND Course='" & [Course] & "' AND [Date]=#" & [Date] & "# AND Location='" & [Location] & "'", "", "; ") AS CR
    FROM Table1
    GROUP BY Instructor, Course, Date, Location, ConcatRelated("Comment", "Table1", "Instructor='" & [Instructor] & "' AND Course='" & [Course] & "' AND [Date]=#" & [Date] & "# AND Location='" & [Location] & "'", "", "; ");

    or

    SELECT DISTINCT Instructor, Course, [Date], Location, ConcatRelated("Comment","Table1","Instructor='" & [Instructor] & "' AND Course='" & [Course] & "' AND [Date]=#" & [Date] & "# AND Location='" & [Location] & "'","","; ") AS CR
    FROM Table1;


    Alternatively, build a report and use its Sorting & Grouping features. Call the ConcatRelated() function in a textbox.

    Date is a reserved word. Advise not to use reserved words as names for anything.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Phil Hoop is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Location
    Edina, MN
    Posts
    9
    June 7: Thank you, thank you, Thank you. You saved my sanity...............................Phil Hoop

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

Similar Threads

  1. Concatenate multiple records into one
    By snipe in forum Queries
    Replies: 1
    Last Post: 09-30-2015, 01:47 PM
  2. Replies: 5
    Last Post: 03-11-2013, 03:12 PM
  3. Replies: 1
    Last Post: 03-13-2012, 06:11 PM
  4. Replies: 3
    Last Post: 08-16-2011, 02:20 PM
  5. Concatenate two fields (text & number) for key field
    By Larry Elfenbein in forum Forms
    Replies: 2
    Last Post: 11-10-2005, 07:45 AM

Tags for this Thread

Posting Permissions

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