How to display datas in a table in the columns in a query from another table?-CodePudding

In access 2016, I have 3 tables :

TbProducts

productId  |  descriptionProduct
   1       |     PC1         
   2       |     PC2

TbCaracteristics

CaracId    |  DescriptionCarac
   1       |     Motherboard size         
   2       |     Processor               
   3       |     Color      
   4       |     Size

TbCaracteristicsProducts

productId  |     CaracId     |   Value
   1       |     1           |   ATX 
   1       |     2           |   i5
   1       |     3           |   Black
   1       |     4           |   Big tower
   2       |     1           |   MiniITX 
   2       |     2           |   i7
   2       |     3           |   Blue
   2       |     4           |   Big tower

What I would want to display :

Product      |     Motherboard size     |   Processor    |    Color    |    Size
   PC1       |     ATX                  |   i5           |    Black    |   Big tower  
   PC2       |     MiniITX              |   i7           |    Blue     |   Big tower

I tried making a query to have a list of caracteristics but I have no idea how to put them in columns in another, nor linking the informations from a third table... Any idea ?

I also tried to use min(value) and max(value), but the problem is I have more than 2 caracteristics.

CodePudding user response：

Looks like a basic JOIN and CROSSTAB.

TRANSFORM First(TbCaracteristicsProducts.Value) AS FV
SELECT TbProducts.descriptionProduct
FROM (TbCaracteristics INNER JOIN TbCaracteristicsProducts ON TbCaracteristics.CaracID = TbCaracteristicsProducts.CaracId) 
INNER JOIN TbProducts ON TbCaracteristicsProducts.ProductId = TbProducts.ProductID
GROUP BY TbProducts.descriptionProduct
PIVOT TbCaracteristics.DescriptionCarac;

BTW, misspelling "caracteristic" - missing the "h".