MS Access SQL : How to create 2 columns filled with different filters from another column?

Question

Unfortunately, I have to work on a very badly built table. Here is what it looks like:

init table

Product-code	Criteria	Value
10184149	Blue Grid	12.0
10184149	Red Grid	13.0

I've tried several approaches but I just can't seem to get it right, like :

SELECT 
    IIF(([public_report_product].[Criteria]="Blue Grid"),
         [public_report_product].[Value]) AS Blue_Grid,
    IIF(([public_report_product].[Criteria]="Red Grid"),
         [public_report_product].[Value],Null) AS Red_Grid,
    public_dosa.product_code
FROM 
    public_report_product 
INNER JOIN 
    public_dosa ON public_dosa.product_code) = public_report_product.[Product code]
WHERE 
    public_report_product.[Criteria] = "Blue Grid" OR 
    public_report_product.[Criteria] = "Red Grid";

I have of course many other lines with the same "Product code" in this table.

I would like something like this as a result:

Result I want

Product-code	Blue Grid	Red Grid
10184149	12.0	13.0

Thank you in advance.

Answer 1

A solution to this would be to use a crosstab query like this:

TRANSFORM First(Table.Value) AS Value
SELECT Table.product_code
FROM Table
GROUP BY Table.product_code
PIVOT Table.Criteria;