concatenate related records in MS Access

Question

I have tables tbl_main_new_registration and tbl_supp_compositions in my product registration database in Microsoft Access 365.

I am trying to create a sql query so that it can show me brand_name (this field is in the table 1) with all of its active_ingredients (this field in the table 2) in a single row. however, I am unable to figure it out. currently, my query is:

SELECT tbl_main_new_registration.[brand_name], tbl_supp_compositions.active_ingredient, tbl_supp_compositions.strength
FROM tbl_main_new_registration 
INNER JOIN tbl_supp_compositions 
ON tbl_main_new_registration.new_reg_id = tbl_supp_compositions.brand_name;

and it shows the following results:

and I want to achieve it as follows:

I have tried some online solutions i.e. http://allenbrowne.com/func-concat.html but I am unable to understand and apply it. Moreover, I tried to apply some answers from stackoverflow but i think I am unable to fully understand it.

Answer 1

This query using my DJoin function will do that:

SELECT 
    tbl_main_new_registrations.Id, 
    tbl_main_new_registrations.[Brand Name], 
    DJoin("[active_ingredient] & ': ' & [strength]","[tbl_supp_compositions]","[BrandNameId] = " & [Id] & "",": ") AS active_ingredients
FROM 
    tbl_main_new_registrations
GROUP BY 
    tbl_main_new_registrations.Id, 
    tbl_main_new_registrations.[Brand Name], 
    DJoin("[active_ingredient] & ': ' & [strength]","[tbl_supp_compositions]","[BrandNameId] = " & [Id] & "",": ")
ORDER BY 
    tbl_main_new_registrations.Id;