Results 1 to 13 of 13
  1. #1
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62

    Thumbs up Concatenate Multiple Rows for Excel Vlookup

    Hi All,



    Please let me know if this is possible.

    I would like to create a query that does the following:

    1) Field 1 would be a concatenation of Tracking_Number & Invoice_Number
    2) Field 2 would be a concatenation of Charge_Descriptions, Charges, Carriage Returns and Numbered List

    Code:
    Field 1                                                                Field 2
    1Z662F51DK0X25X9921325X12365    1) Entry Prep Fee 28.75 & Chr(10) & 2) Disbursement Fee 11.00 & Chr(10) & 3) Pre-Release Notification Surcharge 12.50 & Chr(10) & 4) Merchandise Processing Fee 2.10
    If tracking number is not populated, only invoice number should be used for field 1.

    This query will be called in Excel through vba and the results will be a table that is referenced from another sheet.

    Please see the attached example data set: Example_v1.zip

    Thanks in advance for your help!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Concatenation of fields in query is simple. Concatenating multiple records is more complicated and often accomplished with VBA. Review http://allenbrowne.com/func-concat.html

    However, might be accomplished with a CROSSTAB query and then a SELECT query using the CROSSTAB as source. This would require a unique record identifier field, such as autonumber.

    Why do you need to involve Excel?
    Last edited by June7; 06-13-2019 at 05:01 PM.
    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
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I imported your data to Access and allowed Access to create a unique ID autonumber field. Attempts to use non-VBA approach just crashed Access. Tested Allen Browne function on 16 records from your data and it works but could perform slowly on large dataset (I did not test with your full sample).

    SELECT [tracking_number] & [invoice_number] AS F1, ConcatRelated("charge_description & ' ' & Format(net_amount,'Fixed')","Example1","Not charge_description Is Null AND net_amount>0 AND [tracking_number] & [invoice_number]='" & [tracking_number] & [invoice_number] & "'","Chr(13) & Chr(10)") AS F2
    FROM Example1
    GROUP BY [tracking_number] & [invoice_number], ConcatRelated("charge_description & ' ' & Format(net_amount,'Fixed')","Example1","Not charge_description Is Null AND net_amount>0 AND [tracking_number] & [invoice_number]='" & [tracking_number] & [invoice_number] & "'");

    Getting the "x) " in front of each item and vbCrLf after will require modifying the VBA.
    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.

  4. #4
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Quote Originally Posted by June7 View Post
    I imported your data to Access and allowed Access to create a unique ID autonumber field. Attempts to use non-VBA approach just crashed Access. Tested Allen Browne function on 16 records from your data and it works but could perform slowly on large dataset (I did not test with your full sample).

    SELECT [tracking_number] & [invoice_number] AS F1, ConcatRelated("charge_description & ' ' & Format(net_amount,'Fixed')","Example1","Not charge_description Is Null AND net_amount>0 AND [tracking_number] & [invoice_number]='" & [tracking_number] & [invoice_number] & "'","Chr(13) & Chr(10)") AS F2
    FROM Example1
    GROUP BY [tracking_number] & [invoice_number], ConcatRelated("charge_description & ' ' & Format(net_amount,'Fixed')","Example1","Not charge_description Is Null AND net_amount>0 AND [tracking_number] & [invoice_number]='" & [tracking_number] & [invoice_number] & "'");

    Getting the "x) " in front of each item and vbCrLf after will require modifying the VBA.

    Thank you so much! I didn't see your reply until I was coming back to say thank you for setting me on the right path. I just figured it out.


    Code:
    SELECT tracking_number & invoice_number AS Tracking_Invoice, iif(len(Tracking_Invoice) = 15, Concatrelated(
    "iif(trim(Miscellaneous_Line_1) &  trim(Miscellaneous_Line_2) & trim(Miscellaneous_Line_3) & trim(Miscellaneous_Line_4) & trim(Miscellaneous_Line_5) is null,charge_description & ' ' & format(net_amount, 'Currency'),Miscellaneous_Line_1 &' ' &  Miscellaneous_Line_2 & ' ' & Miscellaneous_Line_3 & ' ' & Miscellaneous_Line_4 & ' ' & Miscellaneous_Line_5 & ' ' & format(net_amount, 'Currency'))"
    , "shipment_details", 
           "tracking_number & invoice_number = '" & tracking_invoice & 
    "' and charge_category_code in ('adj','mis') and tracking_number is null"
    , "tracking_number", " & Char(10) & "),Concatrelated("charge_description", "shipment_details", 
           "tracking_number & invoice_number = '" & tracking_invoice & 
    "' and charge_category_code in ('adj','mis')"
    , "tracking_number", " & Char(10) & ")) AS Charge_Description
    FROM shipment_details
    WHERE charge_category_code in ('adj','mis') and tracking_number is null
    GROUP BY tracking_number & invoice_number;

  5. #5
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    New problem....

    Any ideas on how I can store the query above in a vba variable? MS Query Builder doesn't all the Concatrelated function and I am having trouble making a string out of the query because of the double quotes and ampersands.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not sure what you mean. MS Query Builder doesn't what?

    Why save to a variable?
    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
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    MSQRY32.EXE (creating a dqy file) gives an error message when trying to import the query from Access -- it does not like the function concatrelated (same way it doesn't like Nz).

    I scrapped that idea and now I am trying to store the query as a string variable, so I can call it in the vba code using:

    Code:
    myQuery = 'This is where I want to put the query'
    
    Set RecSet = db.OpenRecordset(myQuery, dbOpenDynaset)

    The problem is that the string contains ampersands, single quotes and double quotes. I am not sure how to create a string variable that contains the complete query.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Perhaps you should write the concatenated data to a 'temp' table then export that table. Create the table with the necessary fields then run INSERT INTO SELECT action.
    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.

  9. #9
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Quote Originally Posted by June7 View Post
    Perhaps you should write the concatenated data to a 'temp' table then export that table. Create the table with the necessary fields then run INSERT INTO SELECT action.
    When is this action taken?

    Here are the current steps:

    1) in access, the user is prompted to enter csv
    2) csv gets loaded to a table
    3) user is asked if they would like to enter more csv
    4) if yes, go to step 1, if no close access (compact and repair)
    5) excel reports are generated, using vba.

    somewhere before the reports are generated, I need to have the temp table populated so that it can be referenced when running a certain report.

  10. #10
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Quote Originally Posted by NSearch View Post
    When is this action taken?

    Here are the current steps:

    1) in access, the user is prompted to enter csv
    2) csv gets loaded to a table
    3) user is asked if they would like to enter more csv
    4) if yes, go to step 1, if no close access (compact and repair)
    5) excel reports are generated, using vba.

    somewhere before the reports are generated, I need to have the temp table populated so that it can be referenced when running a certain report.
    I think I got it --- when the user is asked if they want to enter another CSV and they select No, the following is executed:

    Code:
        strTable = "temp"
        strSql = "DROP TABLE " & strTable
        CurrentDb.Execute strSql, dbFailOnError
        
        DoCmd.SetWarnings False
        CurrentDb.Execute "Make_Table_Query"
        DoCmd.SetWarnings True

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Visited this again. The SQL I posted shows trying to use Chr(13) & Chr(10) in function strSeparator argument but that doesn't work and forgot to remove. Can't see a way to pass these non-printing characters via argument so modify the ConcatRelated function to use vbCrLf in place of variable strSeparator. Actually, can set the variable to vbCrLf:

    strSeparator = vbCrLf
    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.

  12. #12
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    June7,

    Are you able to use the Concatrelated function with a linked table?

    I have a linked table over ODBC and when I replace the shipment_details table with the linked workspace table, the query returns no results. If I return the query back to the original (with shipment_details (a local table)) it works fine.

    Thanks for the feedback.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Works for tables linked by Access link manager, don't know about ODBC.
    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. Concatenate Multiple Rows into one field
    By shank in forum Access
    Replies: 4
    Last Post: 01-17-2019, 09:08 AM
  2. concatenate rows into one
    By PaulGoer in forum Access
    Replies: 7
    Last Post: 08-17-2018, 02:48 AM
  3. Concatenate Rows into one row
    By rkalapura in forum Programming
    Replies: 4
    Last Post: 04-23-2015, 06:52 PM
  4. Help needed - Trying to concatenate rows
    By clarkian11 in forum Queries
    Replies: 15
    Last Post: 06-09-2011, 10:11 AM
  5. Concatenate Multiple Rows into One
    By Knarly555 in forum Queries
    Replies: 5
    Last Post: 11-12-2010, 06:51 PM

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