Results 1 to 7 of 7
  1. #1
    Josha is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2019
    Location
    Victoria, Australia
    Posts
    42

    How to concat column data in a query?

    So I have a relatively simple query (well I thought it was simple)....

    See fig 1...

    When I run the query I get what you see in Fig 2.

    Problem with it is the caseworker field is from a one-to-many relationship, meaning there can be many ItemCNID linked to multiple caseworkers. So I get the first two rows of data with the exact same data except the caseworker column.... Is there a way in the query to concat the casewoker field, so that instead of having the first two rows (as seen in Fig 2), I only have one row and the caseworker field is concat to be something like JRS, TLR???



    There must be a way, I just can't figure it out!

    Click image for larger version. 

Name:	fig 1.png 
Views:	27 
Size:	24.4 KB 
ID:	48474Click image for larger version. 

Name:	fig 2.png 
Views:	25 
Size:	24.0 KB 
ID:	48475

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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
    Josha is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2019
    Location
    Victoria, Australia
    Posts
    42
    Thanks June7!

    So I added the module as described and then I added the following code in my query (thinking it was going to work perfectly)

    Code:
    =ConcatRelated("Caseworker","tbl_31_exby","ITEMCNID = " & [ItemCnID])
    but I get this error when I run the query.... Click image for larger version. 

Name:	fig 3.png 
Views:	24 
Size:	28.2 KB 
ID:	48476Click image for larger version. 

Name:	fig 3.png 
Views:	24 
Size:	28.2 KB 
ID:	48476

    Am I doing something wrong?

    I checked that my Access database could understand the module by compiling....

    I'm at a loss

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Self explanatory really.
    Show your concat code. I suspect you will need to qualify the field with the table name?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The query includes two tables with field [ItemCnID]. Specify which table with table name prefix. Probably the table where the field is primary key.

    tbl_30_ItemCaseNotes!ItemCnID
    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.

  6. #6
    Josha is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2019
    Location
    Victoria, Australia
    Posts
    42
    What you are saying makes - I need to specify which table..... But how do I do that?

    I tried this code.... But it didn't work I got the same error...

    Code:
    =ConcatRelated("Caseworker","tbl_31_exby","[tbl_30_ItemCaseNotes]![ItemCnID]","ITEMCNID = " & [ItemCnID])
    Click image for larger version. 

Name:	expression builder.png 
Views:	11 
Size:	46.6 KB 
ID:	48491

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Specify which table field from the query for the parameter input - the concatenated field.

    =ConcatRelated("Caseworker", "tbl_31_exby", "ITEMCNID = " & [tbl_30_ItemCaseNotes]![ItemCnID])
    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.

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

Similar Threads

  1. Replies: 3
    Last Post: 05-14-2020, 04:11 PM
  2. Concat field values in crosstab query?
    By stalk in forum Queries
    Replies: 5
    Last Post: 05-10-2019, 12:28 PM
  3. paste excel column data into query?
    By ruiter in forum Access
    Replies: 21
    Last Post: 05-02-2016, 10:39 AM
  4. How to copy data from one column to other using query.
    By Sumanth.Ganjam in forum Access
    Replies: 3
    Last Post: 05-11-2014, 11:40 AM
  5. SQL Update query using data in Excel column
    By tylerg11 in forum SQL Server
    Replies: 2
    Last Post: 09-03-2013, 05:10 PM

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
  •  
Other Forums: Microsoft Office Forums