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

[racefan91]

Ok, so I'm finally getting back around to looking at this. I have it somewhat working. I have my MovieActorJunction table which has MovieID and ActorID.

Here is my code

SELECT tblMovies.MovieName, ConcatRelated("ActorID","tblMoviesActorsJunction", "MovieID = " & [MovieID]) AS Actors
FROM tblMovies;

This produces the movie name called out in Text but the Actors show up as the ID number

MovieName Stars
Jurassic World 1, 2, 3

Is there a way to get the numbers to show up as the Actor Names?

[orange]

Would be better if you showed us your Table designs. Sounds like you may have lookup field in a table.

[CJ_London]

suspect you need a query based on the junction table and the actors table and use that in your concatrelated function

[Welshgasman]

Ok, so I'm finally getting back around to looking at this. I have it somewhat working. I have my MovieActorJunction table which has MovieID and ActorID.

Here is my code

SELECT tblMovies.MovieName, ConcatRelated("ActorID","tblMoviesActorsJunction", "MovieID = " & [MovieID]) AS Actors
FROM tblMovies;

This produces the movie name called out in Text but the Actors show up as the ID number

MovieName Stars
Jurassic World 1, 2, 3

Is there a way to get the numbers to show up as the Actor Names?

I would have thought you would want ActorName and not ActorID?
Hence the numbers.

[June7]

As CJ said, build and save a query object that joins tblMoviesActorsJunction and tblActors to pull actor names associated with each movie. Reference that query in the ConcatRelated function as well as actor name field.

[Gicu]

Might want to review this function as well:
https://www.accessmvp.com/thedbguy/c...itle=simplecsv
Cheers,