Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    Many to many

    All, using access 2016..I have a report with two fields based on a m:m which I resolved with a junction table. On my report I list many clients with the same assignmentID. This is fine but when I have the same client with different assignmentIDs it duplicates the entire record detail. I'm only printing the assignmentID with no other fields but the clients have fields showing more client information. How can I print this record and have the assignmentID in the same line with maybe a , to separate?

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I'm only printing the assignmentID with no other fields but the clients have fields showing more client information.
    THAT is a confusing statement. Perhaps show us some images of the report and what's wrong with it.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    How can I print this record and have the assignmentID in the same line with maybe a , to separate?
    suspect you need to use Allen Brownes concatrelated function http://allenbrowne.com/func-concat.html

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think Ajax has guessed your need and provided a solution. But, I find your post confusing, as did Davegri, and would appreciate an example to clarify.
    Good luck with your project.

  5. #5
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Sorry for the confusion.
    Report looks like this:

    ClID. CNam. AssignID
    1320- Perk- 23
    1320- Perk- 42

    Sorry, code tags don't work on my phone for some reason...I would like the AssignID on the same line.
    1320- Perk- 23, 42
    Thanks

  6. #6
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    I tried Allen Brown ConcatRelated. I created the function. I created a text box on my report and put in the control Source... =ControlRelated("ClID","qryRecord","SID= "&"[SID]")
    It does do anything....where am I failing??
    Last edited by slimjen; 07-15-2019 at 01:30 PM. Reason: Missed word

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Too many quotes (also note you changed the function name?):

    =ControlRelated("ClID","qryRecord","SID= " & [SID])
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Sorry; auto complete on phone...
    =ConcateRelated("ClID","qryRecord","SID= " & [SID])
    Still doesn't work...Could it be the problem with my textbox on the report? I made it invisible.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What happens if you make that text box visible? Easy to test, right?
    What exactly do you mean by "doesn't work"?

  10. #10
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Duh! I was making the text box invisible on the main form as I would a text box linking two subforms together....so; once I made it visible and corrected the controlsource; it worked as expected meaning it showed up on the report. But; it still shows twice:
    Code:
    1320-    Perk-  23, 42   
    1320-    Perk-  23, 42
    I guess I need to work on eliminating dups from the query itself. Thanks all

  11. #11
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Ok...I can't seem to find a way to remove the duplicate line effectively. The concatrelated code works in the report but I guess because it's based in a query, that has duplicates, it repeats the line in the report. When I try to put the Concatrelated code at the query level, I can't get it to work. I get an error: 3061 too few parameters expected 1. My fields in the code are string not number and I followed Allen Browns suggestions on quotes around strings. Why would it work in the report but not in the query. The other thing is I can hide duplicates in the report property but has mixed results. Some assistance pls.

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    what is your actual query that is creating the duplicates - show the whole sql

  13. #13
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Below is the Sql for the query. Sorry, the code tags are not visible on my phone:
    Select tblStype.SID, tblSType.Sname, tblSType.SDescrip, tblAssign.AssignID, tblSap SID, tblCntrl.ClID, tblCntrl.CName, Concatrelated("AssignID","qryRecord","ClID=""" &[ClID] & """") AS CntrlRecord
    From tblSType INNER JOIN ((tblCntrl INNER JOIN tblSap on tblCntrl.SapID = tblSap.SapID) INNER JOIN tblAssignCntrlJunction on tblCntrl.ClID=tblAssignCntrlJunction.ClID) on (tblSType.SID = tblSap.SID) And (tblSType.SID = tblCntrl.SID);
    Once I corrected the Concatrelated code and moved it to the detail section on the report, it worked but still duplicated the lines because if the duplicate in the query. Thanks

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    that's a lot of fields that could cause duplicates

    for some reason you have a table 'tblAssignCntrlJunction' which isn't doing anything, you are not selecting any fields and it doesn't act as a connector to another table. So suggest take it out

    You can also change your SELECT to SELECT DISTINCT which will remove true duplicates.

    Otherwise, remove one field at a time until you have identified what is causing the problem.

    Once you have done that, add back your concat related function to the query

    but I do struggle to see how that is working for you anyway

    =ControlRelated("ClID","qryRecord","SID= " & [SID])

    since your query returns two SID's -
    tblStype.SID & tblSap SID

    and now your query has a difference set of parameters

    Concatrelated("AssignID","qryRecord","ClID=""" &[ClID] & """")

    on the basis that your ID's are numeric and you have another ClID field in one of your other tables try

    Concatrelated("AssignID","qryRecord","ClID=" & tblCntrl.[ClID])

  15. #15
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    I corrected the Concatrelated statement because I didn't have the correct fields. Now I do. ClID and AssignID are strings unfortunately because they contain one letter I tried distinct and thats when I get the error but still dups...I'll try to remove fields n add back as u suggested

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

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