Remove blanks from a crosstab query in another query

[racefan91]

I have a table with movie ratings where the name of the movie is in the first column and the second column is the rating for the song. I want to create a query where all the different ratings are the column headings and then the movie name goes in the column of how it rated. When I created my crosstab query there are blank cells in each row for a specific movie except the column that has the movie title. Is there a way to make a query that gets rid of all the blank cells?

This is how the crosstab looks





This is what I want to achieve

[madpiet]

I have a table with movie ratings where the name of the movie is in the first column and the second column is the rating for the song. I want to create a query where all the different ratings are the column headings and then the movie name goes in the column of how it rated. When I created my crosstab query there are blank cells in each row for a specific movie except the column that has the movie title. Is there a way to make a query that gets rid of all the blank cells?

This is how the crosstab looks



This is what I want to achieve

Got some data? I'm not 100% sure how you'd put non-numeric data in the VALUES section. If it were Excel or PowerBI, I'd do something like CONCATENATEX('Movies',[MovieTitle],", ") in there. In a word, I wouldn't do it Abscess. The pus just gets everywhere. No really, it's super simple in Excel. I guess you'd just use CrLf as the delimiter.

Movie List:=CONCATENATEX('Table1_1',[Movie]," ",[Movie])

(well, instead of the space " " I'm using.

[mike60smart]

Hi
Are you able to upload a copy of the database?

[CJ_London]

I'm not 100% sure how you'd put non-numeric data in the VALUES section

by using first/last/min/max

@racefan - would need to see your table and crosstab sql to see if it could be solved in access

[June7]

Right, what is the GROUP BY parameter that these movie names would be associated with? A CROSSTAB requires 3 fields so what is your SQL?

Provide db or build a table in post (click Go Advanced to get the table builder toolbar). Can even copy/paste records.

[June7]

You must be using unique record ID field as RowHeader to accomplish that CROSSTAB. That would certainly result in a lot of blanks. Getting the output you desire is tricky. One approach uses DCount() domain aggregate function to calculate a RowHeader field.

TRANSFORM First(MovieName) AS FirstMovieName
SELECT Val(DCount("*","Movies","ID<=" & [ID] & " AND Rating=" & [Rating])) AS R
FROM Movies
GROUP BY Val(DCount("*","Movies","ID<=" & [ID] & " AND Rating=" & [Rating]))
PIVOT Movies.Rating IN(10,9,8,7,6,5,4,3,2,1);

R	10	9	8	7	6	5	4	3	2	1
1	Movie1	Movie4		Movie2			Movie3
2	Movie5						Movie6