Originally Posted by
June7
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;