Access, for each record row-CodePudding

I want to make a query in access where for each record there will created a new column. No idea how to do this. It's also hard to Google if you don't know where to google for.

As an example I have made a simple database, see the above example.

Now I want to make a query that will put all the possible colors in a column and the cars in a row. Something like this.

And tips or hints to get me in the right direction?

See above for what i'm expecting

CodePudding user response：

I assume that ColorOfCar is a multi-valued-column. You can use a pivot query like this:

TRANSFORM Count(*)
SELECT Cars.Car
FROM Cars
GROUP BY Cars.Car
PIVOT Cars.ColorOfCar.Value;

You can specify the order of the rows and columns like this

TRANSFORM Count(*) AS Expr1
SELECT Cars.Car
FROM Cars
GROUP BY Cars.ID, Cars.Car
ORDER BY Cars.ID
PIVOT Cars.ColorOfCar.Value In 
  ("Red","Green","Blue","Cyan","Yellow","Purple","Black")

Here the rows are sorted by Cars.ID and the value columns are ordered as specified in the list.