I understand that my question is not clear, let me explain.

In an Access database, I have a table TbProduct:

productId  |  descriptionProduct
   1       |     Cable         
   2       |     Mouse         
   3       |     Keyboard        
   4       |     Screen
   5       |     Set1       
   6       |     Set2        
   7       |     Set3  

I have a table TbCompose:

productIdComposed  |  productIdComposing
   5               |     1         
   5               |     2         
   6               |     1       
   6               |     4
   7               |     3       
   7               |     4        

Those 2 IDs in the TbCompose are foreign keys joined with one to many on the ID in the table TbProduct.

Is it possible to make a query to display my data with every product in the set in a different column? Like this:

descriptionProduct |  ProductComp1   | ProductComp2
   Set1            |     Cable       |     Mouse
   Set2            |     Cable       |     Screen
   Set3            |     Keyboard    |     Screen

For information, every set are made of 2 products, no more, no less, and let's assume I can't separate the sets in another table, because I have other functionalities that needs them to be considered as products.

CodePudding user response：

select   tp.descriptionProduct
        ,min(tp2.descriptionProduct) as ProductComp1
        ,max(tp2.descriptionProduct) as ProductComp2
from     TbProduct tp join TbCompose tc on tc.productIdComposed = tp.productId join TbProduct tp2 on tc.productIdComposing = tp2.productId
group by tp.descriptionProduct

Fiddle