Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133

    Show multiple Values in a Query, based off another query value?

    Hello everyone, I have been stumped recently and am in need of help.

    #Scenario

    I have set up a system using Forms to add employee Discrepancies(errors @ work) to a table (eventually to be printed as a report each month). The fields that need to show on the report are; "Error Name", "Number of times conditioned", "Employees who were conditioned".

    #Problem

    So far I have the report printing almost like this using a query except I do not know how to show multiple employee names next to the same condition, for instance:

    What I need:

    Spilled Coffee::::::::4::::::::John, Mary, Ted

    Right now it's showing:

    Spilled Coffee:::::::2:::::::John


    Spilled Coffee:::::::1:::::::Mary
    Spilled Coffee:::::::1:::::::Ted

    The employee name and condition are multi valued fields BUT they are entered in using a form one at a time through out a given day, I have set the 'Totals" in the query to:

    (Group by:Condition.value)
    (Count:Condition.Value)
    (Group By:employeename.Value)

    I will be active on here all day to get this resolved as it is important, I know this is vague and that most experienced members will have questions prior too please just ask I will respond quickly.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    So Should I use the control source or apply the Module to the Query?

    I decided to try the control source first since it is just 2 reports, I entered:

    =ConcatRelated("[pcConditioned Name]", "pcreports", "[Condition Number] = " & [Condition Number])

    It keeps asking for the Value of my Key? "Condition Number"
    Last edited by Forbes; 03-10-2017 at 01:48 PM.

  4. #4
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    =ConcatRelated("pcreports.[pcConditioned Name].Value","pcreports")

    returns the field with every name if I leave the sort blank.. but when I add

    =ConcatRelated("pcreports.[pcConditioned Name].Value","pcreports","[pcreports]![Condition Number] = " & [pcreports]![Condition Number])

    It asks for a Value for "Condition Number" and I am not sure what it wants.. or what I should put... so close anyone know?

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The parameter prompt is Access telling you it can't find something. Double check the spelling of the field. You also don't need to specify the table, so more like:

    ConcatRelated("[pcConditioned Name]","pcreports","[Condition Number] = " & [Condition Number])
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    That makes sense, I checked all my spelling & tried a few misc other things (Reverting after they didn't work of course) I'm still receiving the same dialog, maybe it's my misunderstanding of the foreign Key and Primary?
    --------------------------------------------------------------------------------------------------------------------------------------------------------------
    With this:

    =ConcatRelated("[pcConditioned Name]","pcreports","[Condition Number] = " &) I get all of the names that were conditioned regardless of the condition name.

    With:

    ConcatRelated("[pcConditioned Name]","pcreports","[Condition Number] = " & [Condition Number]) I get the parameter prompt again for "Condition Number"
    or
    ConcatRelated("[pcConditioned Name]","pcreports","[pcCondition] = " & [Condition Number]) same thing as above (random attempt at thought)

    Report columns:

    Condition:::::Condition #"Count"::::::Employees that were conditioned for this
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------
    I'm not sure if it matters the Condition Number is the "auto Number Primary Key" in pcreports table
    If there is any other information you want from me to make troubleshooting this easier let me know, I appreciate your help and patients.

  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
    Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    I am struggling to get the websites uploader to function, it errors out every time I attempt to upload the file will continue to try other methods.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Do a compact repair, then zip it. That will usually make it small enough to load.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Attachment 27817


    Here ya go

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I think it has to do with the fact that the pcCondition field is multivalue. I haven't used one (and won't). If I add a text field to that table with the value "Saves everything on desktop", and use that field in the report instead of the other, this works:

    =ConcatRelated("[pcConditioned Name]","pcreports","[TestField] = '" & [TestField] & "'")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    By the way, you didn't want to use the autonumber field [Condition Number] for the criteria. It is unique, thus would never have returned multiple values. It wasn't a field in the report, which is why you got the parameter prompt.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Thanks Paul I appreciate it, still pretty new is it possible to make the pcreport form input the field still? using text or another method perhaps

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Where you using the multivalue feature? You can certainly use the form, it's a matter of whether you can use this concatenation method with a multivalue field. If you're not using it, switch to a plain text field and it should work fine.

    Dinner time here, so I may not be checking in for a while.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Hey Paul, I've been pretty tied up these last 2 days, I am working diligently today to fix these issues and replace my multi with lists instead, for the form to accept the selection as a new record the control source must be set to the report correct? This will be marked solved today upon finishing this project.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-26-2016, 02:12 PM
  2. Replies: 5
    Last Post: 03-08-2016, 07:25 AM
  3. Change Table Index Values Based on query Values
    By thuzkee02 in forum Import/Export Data
    Replies: 2
    Last Post: 11-24-2015, 11:45 PM
  4. Filtering query based on multiple rows values
    By GeorgeB in forum Queries
    Replies: 6
    Last Post: 12-05-2013, 06:17 PM
  5. Replies: 2
    Last Post: 05-16-2012, 04:45 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