Results 1 to 14 of 14
  1. #1
    racefan91 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2013
    Posts
    56

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

    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

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,512
    Can you post a copy of your db
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,342
    sounds like you need to use a crosstab query

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

  4. #4
    racefan91 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2013
    Posts
    56
    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"?

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,342
    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

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,734
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    racefan91 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2013
    Posts
    56
    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

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,734
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    racefan91 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2013
    Posts
    56
    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?

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,704
    Would be better if you showed us your Table designs. Sounds like you may have lookup field in a table.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,342
    suspect you need a query based on the junction table and the actors table and use that in your concatrelated function

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,738
    Quote Originally Posted by racefan91 View Post
    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.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,734
    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.
    Last edited by June7; 01-19-2024 at 05:03 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,085
    Might want to review this function as well:
    https://www.accessmvp.com/thedbguy/c...itle=simplecsv
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-20-2021, 09:57 AM
  2. Replies: 13
    Last Post: 05-10-2017, 12:43 AM
  3. Replies: 3
    Last Post: 05-18-2015, 08:46 PM
  4. Replies: 7
    Last Post: 06-04-2013, 01:10 PM
  5. Replies: 0
    Last Post: 11-23-2009, 09:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums