How to query or report a junction table in a single line?

[racefan91]

I am working on converting my movie list from excel to access. I created separate tables for my movies and actors and then joined them in a junction table. How would I go about displaying the movies in a single line in either a query or report? Right now when I try to build a query for each movie based on the junction table I get multiple rows for each movie since each one has multiple actors.



https://www.accessforums.net/showthread.php?t=79814

I found this database through another post and it kind of does what I want to do in the report rptEventsScheduled, except instead of having the names one below the next I want them to be in one row.

Any help would be appreciated. Thanks

[Bob Fitz]

Can you post a copy of your db

[CJ_London]

sounds like you need to use a crosstab query

alternatively look at using the concatrelated function
http://allenbrowne.com/func-concat.html

[racefan91]

I tried playing around with the crosstab query since that was my original thought. Could you give me some direction to set it up? Do I use the MovieID and ActorID from the junction table and what do i use as my "value"?

[CJ_London]

Could you give me some direction to set it up?

difficult without table/ field names and an idea of what you want it to look like 'displaying the movies in a single line' doesn't really give a clear image. Suggest mock up what you want it to look like

[June7]

I suspect a CROSSTAB is not going to be practical for report output. It is hard to build a stable report based on CROSSTAB because of dynamic field headers.

A CROSSTAB requires 3 fields. It is possible to calculate the 3rd field for use as column headers - the PIVOT clause. Review example in https://stackoverflow.com/questions/...y-in-ms-access

Recommend you look at the Allen Browne alternative.

[racefan91]

Ok, I'll try to explain things a bit further. I have a table with a field MovieName and other information about the movie. I have another table with just ActorName. I created the junction table where I can have multiple actors in the same movie.

MovieName ActorName
Jurassic World Dominion Chris Pratt
Jurassic World Dominion Bryce Dallas Howard
Jurassic World Dominion Jeff Goldblum

How would I go about creating a form or query where it would display like this:
Jurassic World Dominion Chris Pratt Bryce Dallas Howard Jeff Goldblum

[June7]

As already noted, CROSSTAB likely not practical. Could use VBA to concatenate actor names to a single string. Review http://allenbrowne.com/func-concat.html