Urgently need help !!!vba code or query to transpose coloumn to rows

Hi everyone,
I have a table named "Purchase price table"
I have a material number and description and location with different price categories for two years of 2019 and 2020. I am trying to transpose price category for each year into rows. here is how I would like to be :

Hi. Welcome to AWF! Have you tried using a UNION query? Just curious...

Yep, that would involve a UNION query in solution. There is no wizard or designer for UNION. Must type into SQLView of query builder. There is a limit of 50 SELECT.

Problem is, you show multiple columns with same name (Gross Price). Need to fix that first. Also advise not to use spaces in column headers. If you want to store data in a relational database, then really need to normalize data structure. UNION query can rearrange data to normalize.

There are 2 options. UNION structure will be slightly different for each.

1. build UNION then use that in a CROSSTAB - this UNION has most normalized data structure

Query1:

SELECT material, MaterialDesc, location, "Gross Price" AS Category, GrossPrice2019 AS Amt, 2019 AS Yr FROM table1
UNION SELECT material, MaterialDesc, location, "rebate", rebate2019, 2019 FROM table1
UNION SELECT material, MaterialDesc, location, "fee", fee2019, 2019 FROM table1
UNION SELECT material, MaterialDesc, location, "freight", freight2019, 2019 FROM table1
UNION SELECT material, MaterialDesc, location, "Net price", NetPrice2019, 2019 FROM table1
UNION SELECT material, MaterialDesc, location, "Gross Price", GrossPrice2020, 2020 FROM table1
UNION SELECT material, MaterialDesc, location, "rebate", rebate2020, 2020 FROM table1
UNION SELECT material, MaterialDesc, location, "fee", fee2020, 2020 FROM table1
UNION SELECT material, MaterialDesc, location, "freight", freight2020, 2020 FROM table1
UNION SELECT material, MaterialDesc, location, "Net price", NetPrice2020, 2020 FROM table1;

Query2:

TRANSFORM Max(Query1.Amt) AS MaxOfAmt
SELECT Query1.material, Query1.MaterialDesc, Query1.location, Query1.Category
FROM Query1
GROUP BY Query1.material, Query1.MaterialDesc, Query1.location, Query1.Category
PIVOT Query1.Yr;

2. build UNION then use that in an aggregate GROUP BY query

Query1:

SELECT material, MaterialDesc, location, "Gross Price" AS Category, GrossPrice2019 AS [2019], Null AS [2020] FROM table1
UNION SELECT material, MaterialDesc, location, "rebate", rebate2019, Null FROM table1
UNION SELECT material, MaterialDesc, location, "fee", fee2019, Null FROM table1
UNION SELECT material, MaterialDesc, location, "freight", freight2019, Null FROM table1
UNION SELECT material, MaterialDesc, location, "Net price", NetPrice2019, Null FROM table1
UNION SELECT material, MaterialDesc, location, "Gross Price" AS Category, Null, GrossPrice2020 FROM table1
UNION SELECT material, MaterialDesc, location, "rebate", Null, rebate2020 FROM table1
UNION SELECT material, MaterialDesc, location, "fee", Null, fee2020 FROM table1
UNION SELECT material, MaterialDesc, location, "freight", Null, freight2020 FROM table1
UNION SELECT material, MaterialDesc, location, "Net price", Null, NetPrice2020 FROM table1;

Query2:

SELECT Query1.material, Query1.MaterialDesc, Query1.location, Query1.Category, Max(Query1.[2019]) AS MaxOf2019, Max(Query1.[2020]) AS MaxOf2020
FROM Query1
GROUP BY Query1.material, Query1.MaterialDesc, Query1.location, Query1.Category;

As an alternative, consider using Excel Power Query to normalise the data
Then create a crosstab as previously mentioned

For more info, see https://www.access-programmers.co.uk/forums/showthread.php?t=306580