Results 1 to 9 of 9
  1. #1
    srkempka is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    5

    Help with placing multiple values into a single field via query (other ideas welcome!)

    Fist of all, I have only been using Access for little over a week so it is very possible a simple solution exists that I'm unaware of. Other than that, I feel like a have a decent grasp on Access overall.

    I created a database from multiple different tables and a few queries, and my final table/query that tied them all together is massive (like 1.5 million rows big). Despite this, there is no data duplication and every single row is unique. The issue comes from a handful of the columns that can add multiple unique values which causes the data to fork into mostly redundant rows. Here a simple example that shows what I mean:

    Product Type Components
    1 A Material 1
    1 B Material 1
    1 A Material 2
    1 B Material 2

    The issue is non of these different "types" or "components" are mutually exclusive, so the data is completely valid. However, my data set is much more complex and I end up with a crazy amount of rows. I would like to show the data in this form if possible:



    Product Type Components
    1 A, B Material1, Material 2


    Any ideas/suggestions?

    Thanks!

  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
    For a report or something like that, this is a common solution:

    http://allenbrowne.com/func-concat.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Allen's code will not return unique values. You will have build two aggregate queries that return unique values for each product. One query for Type and one for Components. Then another query that calls ConcatRelated function. I expect the query will perform slowly with large dataset.

    SELECT Product, ConcatRelated("[Type]", "TypesUNIQUE", "Product=" & [Product]) AS T, ConcatRelated("Components", "CompsUNIQUE", "Product=" & [Product]) AS C FROM Table1 GROUP BY Product, ConcatRelated("[Type]", "TypesUNIQUE", "Product=" & [Product]), ConcatRelated("Components", "CompsUNIQUE", "Product=" & [Product]);
    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.

  4. #4
    srkempka is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    5
    For simplicity, I've only attempted to do this for one of my "problem" columns so far...but so far I have had little success. I created the query to show unique values like you recommended, but I think the structure of Allen's code is confusing me.

    Since I already have all of my data tied together in a table/query, I attempted to incorporate the code in my end stage so I wouldn't need to backtrack and restructure any of my data. I have a form that lets me search this query of combined data and spits out all of the relevant information. My thought was if I use Allen's text box code (=ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])) on a Report's text box, then it would populate that text box with the multiple values.

    This is what I came up with: =ConcatRelated("[TypesUNIQUE]![Type]","[TypesUNIQUE]","[TypesUNIQUE]![Product] = " & [Forms]![SearchF]![Product])

    where [Forms]![SearchF]![Product] refers to the text box on my search form (which I currently have a value in)

    The reason I have the query names attached to [Type] and [Product] is that I have several different tables with the same field name in this database.

    Any idea what I'm doing wrong? or do I need to start over and do this earlier in the creation of my database?

    (Sorry if this is confusing, its still relatively new to me and I don't have a complete grasp on how to communicate the issues I'm dealing with)

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The query/table name prefix only needed if referenced query pulls multiple fields with same name.

    What does 'little success' mean - error message, wrong result, nothing happens?
    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.

  6. #6
    srkempka is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    5
    So far no errors, but it doesn't pull anything into my report for that section

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

  8. #8
    srkempka is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    5
    Sorry, something else came up that I had to take care of.

    After reading your comment and sitting down and looking at it again I noticed it was trying to display my product field as a number when it is text. I ended up adjusting my code to

    =ConcatRelated("[TypesUNIQUE]![Type]","[TypesUNIQUE]","[TypesUNIQUE]![Product] = """ & [Forms]![SearchF]![Product] & """")

    AND IT WORKED!! Thanks for all of the help guys!

  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
    Happy to help and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Multiple values for a single database field
    By NJH0512 in forum Access
    Replies: 3
    Last Post: 01-30-2018, 07:31 PM
  2. Add multiple values to single textbox
    By Ramtrap in forum Forms
    Replies: 22
    Last Post: 11-09-2017, 10:24 AM
  3. Retrieving Multiple Values into a Single Field
    By Simbiose in forum Queries
    Replies: 6
    Last Post: 08-23-2016, 07:54 AM
  4. Replies: 1
    Last Post: 02-13-2014, 01:50 AM
  5. Replies: 1
    Last Post: 07-07-2009, 01:00 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