Ms-access – Display a one-to-many relationship in one row

ms access

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.

Best Answer

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

Related Solutions

Ms-access – Merge Fields into one Row with a Query

If you have not more than 2 values for domain, this will help:

SELECT [DOMAIN], IIF(First([Q32])<>Last([Q32]), First([Q32])+' '+Last([Q32]),First(Q32)) AS _Q32
FROM [table]
GROUP BY [DOMAIN];

Database Design – Managing Many One-to-Many Relationships Between Two Tables

It is a one to many from the "Children" table; one child to many staff, one child to one or more "ResponsibleAdults" (who could be any familial relationship). If you change Father & Mother fields to be Responsible1, Responsible2 and add Responsible1Relationship, Responsible2Relationship to "Children", you'll solve that problem. Alternatively, you can create a join or link table using just childID, ResponsibleID and relationship (1 or more records per child ID). You'll want a "FamRelationship" lookup table for consistency in your data entry (might be parent, aunt, grandparent, guardian, etc". This way Child Joe can have Parents Mike and Mary, while his cousin child Jane can have Uncle Mike and Aunt Mary. Both Children records would reference the same 2 what you're currently calling "Parents" records.

If a "staff" member is not a member of Board of Directors and it's important to note that, add a tinyint indicator to "staff" (true/false, 1/0).

Best Answer

Related Solutions

Ms-access – Merge Fields into one Row with a Query

Database Design – Managing Many One-to-Many Relationships Between Two Tables

Related Question