1

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:

enter image description here

and I want to achieve it as follows:

enter image description here

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.

James Z
  • 12,209
  • 10
  • 24
  • 44
AgileMethod
  • 13
  • 2
  • can't you do it at application level? – nbk May 01 '23 at 11:04
  • I'm trying to concatenate `active_ingredient` of a `brand_name` along with its relative `strength`s. I don't know how to do that at application level? – AgileMethod May 01 '23 at 11:26
  • i understand that, but access has no function for that, there a plenty of examples like https://stackoverflow.com/questions/8601643/access-sql-query-to-concatenate-rows – nbk May 01 '23 at 11:50
  • Does this answer your question? [Concatenate records and GROUP BY in Access](https://stackoverflow.com/questions/15624845/concatenate-records-and-group-by-in-access) – nbk May 01 '23 at 11:52

1 Answers1

0

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;

enter image description here

Gustav
  • 53,498
  • 7
  • 29
  • 55