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

    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,262
    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
    10,671
    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
    52
    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
    10,671
    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
    51,755
    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
    52
    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
    51,755
    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.

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