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.

1 Answer 1


I could achieve your query in three steps. Assuming your table values are like the values listed below:

enter image description here

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.