Display a one-to-many relationship in one row I have a "one to many" relationship in two tables:

Table "products" contains: product_id, product_descr
Table "packing" contains: product_id, package_id, package_desc


One product has often more than one package. I want to create a query in Microsoft Access, where I get all the package options for one product in one row:

product_id package_id1 package_desc1 packageid2 package_desc2 packageid_n package desc_n


The products do have a different number of packages, it's not a fixed number.
I could achieve your query in three steps. Assuming your table values are like the values listed below:




Step 1: In SQL View paste this query and save it as part1, This is for package_id transpose

TRANSFORM First([packing].package_id)
SELECT [packing].[product_id]
FROM packing
GROUP BY [packing].[product_id]
PIVOT [packing].package_id;

Step 2: In SQL View paste this query and save it as part2, This is for package_desc transpose

TRANSFORM First(packing.package_desc)
SELECT packing.product_id
FROM packing
GROUP BY packing.product_id
PIVOT packing.package_id;

Step 3: Join above two queries based on product_id to get your result