Results 1 to 12 of 12
  1. #1
    Ray_E is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2015
    Posts
    7

    Concatenate values from related records in many-many relationship.

    I am trying to concatenate values from related records, and have looked at Allen Browne’s solution (using DAO), as well as those of Dev Ashish (on The Access Web) and Duane Hookom (using ADO). All of these are for applications with no more than two tables (and 1-many relationships) to get the data from. My problem is I have many-many relationships, so I have a third table in between. The db (originally developed in Access 2000, but I am now using 2007) is used to track and report on the missions our church supports, and show the names of the people in our church who report on those missions.
    There are 3 tables:
    Missions (MissionID as primary key and autonumber Long), Mission_Title (text) and several others, all text but one, which is memo.
    Reporters (ReporterID as primary key and autonumber Long), FirstName, LastName, and phone (all text), and Notes (memo).
    MissionReporters (MissionReporterID as primary key and autonumber Long), MissionID and ReporterID, both Long.
    There are about 25 records in Missions and a few more in Reporters, so it’s a pretty small db, and is in the .mdb format.

    I need to create a report that shows which reporter(s) are assigned to each Mission, and another report showing which missions are assigned to each reporter. I am having difficulty with a query for the first report, having tried Mr. Hookom’s approach, and can’t figure out how to adapt it to a many-many application.
    Data records in Missions: Mission A, Mission B, Mission C, etc. Each unique mission appears once. Data in Reporters: John Smith, Jane Doe, Oscar Myer, etc., with each name appearing once. Data in MissionReporters: All long integers, MissionReporterID being 1-27, and the 2 other fields matching Missions and Reporters.



    I would really appreciate help with the query, as I am now retired and don’t have access to a corporate library, local experts, etc.

    Thanks,
    Ray

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Do you really need to concatenate the related data into one long text string? A normal report can list reporters beneath each mission they are associated with. And another report can do the reverse.

    Allen Browne's code can easily accommodate the concatenation. Since I assume you want the reporters names (or the mission titles) and not just their IDs, build a query that joins MissionReporters with Reporters and Missions so the related info (titles and names) is available and will serve as the source for concatenation. Concatenate the FirstName and LastName fields to create a new field in the query called FullName. Then for each respective report, call the function.

    SELECT *, ConcatRelated("FullName", "QueryName", "MissionID=" & MissionID) AS AllReporters FROM Missions;

    SELECT *, ConcatRelated("Mission_Title", "QueryName", "ReporterID=" & ReporterID) AS AllMissions FROM Reporters;

    Be aware these functions have been known to perform very slow on very large datasets.
    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.

  3. #3
    Ray_E is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2015
    Posts
    7

    Thanks, I'll try it.

    Quote Originally Posted by June7 View Post
    Do you really need to concatenate the related data into one long text string? A normal report can list reporters beneath each mission they are associated with. And another report can do the reverse.

    Allen Browne's code can easily accommodate the concatenation. Since I assume you want the reporters names (or the mission titles) and not just their IDs, build a query that joins MissionReporters with Reporters and Missions so the related info (titles and names) is available and will serve as the source for concatenation. Concatenate the FirstName and LastName fields to create a new field in the query called FullName. Then for each respective report, call the function.

    SELECT *, ConcatRelated("FullName", "QueryName", "MissionID=" & MissionID) AS AllReporters FROM Missions;

    SELECT *, ConcatRelated("Mission_Title", "QueryName", "ReporterID=" & ReporterID) AS AllMissions FROM Reporters;

    Be aware these functions have been known to perform very slow on very large datasets.
    Thanks, I'll give it a try.

    I do need the concatenation, as all of the data will not fit on one line, even with landscape printing. I had only tried Duane Hookom's code, so I'll give Allen Browne's code a try. I have used his stuff in the past, and always with great success. I just wasn't sure how to handle the many-many relationship. But it makes sense the way you put it.

    Thanks much,
    Ray
    Last edited by June7; 02-10-2015 at 04:16 PM. Reason: Update - this is and will be a small dataset (< 50 records).

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    For what it may be worth, I responded to a post recently with a much smaller version, but similar concept, of your many to many with concatenated data. I did use Allen Browne's function. It is much smaller, but may be of some interest.

    Good luck with your project.

  5. #5
    Ray_E is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2015
    Posts
    7

    Error 3306

    Quote Originally Posted by June7 View Post
    Do you really need to concatenate the related data into one long text string? A normal report can list reporters beneath each mission they are associated with. And another report can do the reverse.

    Allen Browne's code can easily accommodate the concatenation. Since I assume you want the reporters names (or the mission titles) and not just their IDs, build a query that joins MissionReporters with Reporters and Missions so the related info (titles and names) is available and will serve as the source for concatenation. Concatenate the FirstName and LastName fields to create a new field in the query called FullName. Then for each respective report, call the function.

    SELECT *, ConcatRelated("FullName", "QueryName", "MissionID=" & MissionID) AS AllReporters FROM Missions;

    SELECT *, ConcatRelated("Mission_Title", "QueryName", "ReporterID=" & ReporterID) AS AllMissions FROM Reporters;

    Be aware these functions have been known to perform very slow on very large datasets.
    Well, I tried your suggestion for AllReporters. My Select statement:

    Expr1: (SELECT Missions.[Mission_Title], ConcatRelated("[Reporter]", "qryMissions", "MissionID=" & MissionID) AS AllReporters FROM Missions (Sorry, I can't remember how to enter code & can't find the answer in the FAQ or elsewhere).

    This is placed in a column in the design view of qryMissions. When I switch to Datasheet view, I get:

    You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field. (Error 3306)


    What am I doing wrong?
    Last edited by Ray_E; 02-10-2015 at 09:45 PM. Reason: Finally found how to enter code. Thanks for your help; I really appreciate it.

  6. #6
    Ray_E is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2015
    Posts
    7
    Hi orange,

    I tried to get your example working, but got lost - it looks like you created multiple queries, with the final query reading the output of the previous one? Where do you put these statements - in separate queries, or successively in the same query? Or??

    Thanks for your help.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Separate queries is what I did. It was easier to use the second query -where I had the numbers and the names as a single field, then used that query and field name in the concatenation function.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The SELECT statements in post 2 would be the RecordSource of reports. They were not intended to be used as a field expression.

    Be sure to change QueryName to whatever you actually name the query.
    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
    Ray_E is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2015
    Posts
    7
    Ah-Ha! I was so stuck on the "Get everything in a query as you need it and then create the report" approach, I never even thought of putting the select statement in the report record source!
    Now that we have power back (after 3 1/2 hrs), IT WORKS!!
    Thank you, thank you, thank you.
    I'll mark this as SOLVED.

  10. #10
    Ray_E is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2015
    Posts
    7
    Now I think I see. I'll try this, although I will go with June7's post, as I already had the needed query.

    Thanks for your help.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Congrats! Nothing like perseverance.
    You might post you final SQL that you used for Recordsource.

  12. #12
    Ray_E is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2015
    Posts
    7
    OK, here it is:

    Code:
    The query SQL:
    
    SELECT Missions.Mission_Title, Missions.Mission_Addr, Missions.Mission_City, Missions.Mission_State, Missions.Mission_ZIP, Missions.Mission_Phone, [MsnryFNames] & " " & [MsnryLName] AS Missionary, [FirstName] & " " & [LastName] AS Reporter, Missions.MissionID, Missions.Alt_M_Phone, Missions.Notes, Missions.email
    FROM Missions INNER JOIN (Reporters INNER JOIN MissionReporters ON Reporters.ReporterID = MissionReporters.ReporterID) ON Missions.MissionID = MissionReporters.MissionID
    ORDER BY Missions.Mission_Title;
    
    and the report Record Source:
    
    SELECT *, ConcatRelated("Reporter", "qryMissions", "MissionID=" & MissionID) AS Reporters FROM Missions;

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

Similar Threads

  1. Replies: 3
    Last Post: 03-22-2014, 04:32 PM
  2. Replies: 9
    Last Post: 01-26-2013, 11:06 PM
  3. Replies: 1
    Last Post: 03-13-2012, 06:11 PM
  4. Avoiding Duplicates - Concatenate Related?
    By WBosman in forum Access
    Replies: 10
    Last Post: 05-18-2011, 09:05 AM
  5. Replies: 3
    Last Post: 10-16-2009, 09:27 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