Results 1 to 5 of 5
  1. #1
    Rendon115 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2019
    Posts
    10

    Query on Many to Many Relationship

    Click image for larger version. 

Name:	ExTable.PNG 
Views:	34 
Size:	12.1 KB 
ID:	42281Click image for larger version. 

Name:	ExJunction.PNG 
Views:	35 
Size:	9.4 KB 
ID:	42282Click image for larger version. 

Name:	ExQuery.PNG 
Views:	35 
Size:	6.9 KB 
ID:	42283



    Some pictures I made up really quick above to ask this question. In this example, when I have a many to many relationship using a junction table, and run a query on it, I get the original [Type] with several different [Color] as separate records in the query. Is there a way either:

    With the query, to combine like records on a certain field i.e. Type, and the combine the rest of the like fields with a "," between them? Such as [Rock][Blue, Yellow, Green]
    Or either in a form display? In my database Im displaying this information in a continuous form layout made to look like a datasheet. The issue comes into play when theres multiple [Type] being displayed in the leftmost column, the continuous form becomes extremely longer than it needs to be.

    Thank you,

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Look at JoinFromArray on my site http://forestbyte.com/vba-code-samples/ or Allen's http://allenbrowne.com/func-concat.html

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Rendon115 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2019
    Posts
    10
    Quote Originally Posted by Gicu View Post
    Look at JoinFromArray on my site http://forestbyte.com/vba-code-samples/ or Allen's http://allenbrowne.com/func-concat.html

    Cheers,
    Neither of these will work, but thank you

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Rendon115 View Post
    Neither of these will work, but thank you
    I don't have experience with Gicu's solution but based on your description of the problem Allen Browne's solutions certainly should work. Why don't you tell us what problems are you having trying to get those solutions to work? Or maybe you could elaborate on your problem further so we can understand why those solutions aren't suitable?

    Here is example usage of Allen Browne's function (tested, looks like it works to me):
    Code:
    SELECT ExTable1.Type, 
           ConcatRelated("ExTable2.Color",
                         "ExTable2 INNER JOIN ExJunction ON ExTable2.ID = ExJunction.ColorID",
                         "ExJunction.TypeID=" & [ExTable1].[ID],
                         "ExTable2.Color",
                         ", ") AS Colors 
    FROM   ExTable1;

  5. #5
    Rendon115 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2019
    Posts
    10
    I have written a ton of VBA code for Access for the database I am working on, but I am not ashamed to admit that SQL is certainly my weakest link. I have barely written any, and mostly use the query designer. I recently have been changing the way the data is being stored from Linked Lists, into tables with junctions, as for better design overall, and more malleable data. Linked lists were proving far too inefficient and troublesome, so for some of my tables I have been making junction tables for many-many relationships.

    The issue above, is the result of these changes. Last week when attempting to use Allen Browne's solution, I had mis-typed a part of the SQL statement, referencing the wrong table and was getting mixed results. I have re-looked at it today with more clarity and corrected it. I appreciate the help, this is what I was looking to do.

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

Similar Threads

  1. Replies: 3
    Last Post: 03-19-2015, 05:26 PM
  2. form / query relationship
    By ariansman in forum Forms
    Replies: 4
    Last Post: 11-12-2014, 07:57 AM
  3. Query relationship structure
    By Juicejam in forum Queries
    Replies: 15
    Last Post: 02-12-2012, 10:26 PM
  4. Query issues due to a bad relationship!
    By annemrosenberg in forum Queries
    Replies: 18
    Last Post: 09-12-2011, 01:50 PM
  5. Query with many-to-many relationship
    By jhollingsh615 in forum Queries
    Replies: 4
    Last Post: 05-18-2011, 11:31 AM

Tags for this Thread

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