Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Aug 2010
    Posts
    87

    Unanswered: Join Data in Column 2 if Column 1's ID is the Same - How Do I?

    Hello,
    I'm stuck on something, maybe you can help.

    I have two columns.

    ColumnSku: Which contains a product sku
    ColumnModel: Which contains a model number

    ColumnSku can contain the same sku hundreds of times
    ColumnModel can contain the same model several times but not for the same sku

    What I need to do is this
    For every time a sku is shown in ColumnSku, take the model in ColumnModel and join them together separated by a comma.

    For example

    ColumnSku | ColumnModel
    SKU1111 | Model11111
    SKU1111 | Model22222
    SKU1111 | Model33333
    SKU1111 | Model44444
    SKU1111 | Model55555
    SKU9999 | ModelHHHHH
    SKU9999 | ModelJJJJJ
    SKU9999 | ModelMMMMM

    Would end up like this
    ColumnSku | ColumnModel
    SKU1111 | Model11111,Model22222,Model33333,Model44444,Model5 5555
    SKU9999 | ModelHHHHH,ModelJJJJJ,ModelMMMMM

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

  3. #3
    Join Date
    Aug 2010
    Posts
    87
    Not really! This is all in one table. I don't even know what that code says.
    I'm hoping for something I can use in SQL view within the dB that creates a new table with the sku and combined models which are separated by the comma like in the example shown

    Thanks for replying though. Any other ideas?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sure:

    Microsoft Access tips: Concatenate values from related records

    which is of course the same concept. It doesn't really matter whether it's two tables or one.
    Paul

  5. #5
    Join Date
    Aug 2010
    Posts
    87
    I think that's the ticket!

    Paul, only because I'm extremely new to this. How would I set that code up for my use?

    I would imagine I need a new table made, say it's called "newtable" and I'm looking at table name "oldtable" with the fields of "sku" and "models".

    new table would take (from oldtable) the sku from the sku column, then concatenate the models and insert them into "newtable"

    Please? LOL I'm scratchin here...

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The code is pretty much copy/paste, as described best on Allen's site. Then you just call it from a query:

    INSERT INTO NewTable (SKU, Models)
    SELECT DISTINCT SKU, ConcatRelated(...)
    FROM OldTable

    That's untested, as you need to restrict to one record. You can generally use DISTINCT or a GROUP BY clause.
    Paul

  7. #7
    Join Date
    Aug 2010
    Posts
    87
    how do I restrict to one record?
    I don't know what a "DISTINCT" or "GROUPBY" clause even looks like.

    Also, what goes where the three dots are? Anything?

    INSERT INTO NewTable (SKU, Models)
    SELECT DISTINCT SKU, ConcatRelated(...)
    FROM OldTable

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The DISTINCT is in there, I just haven't tested.

    You'd put the arguments discussed on the website where the three dots are. Like Allen's example:

    SELECT CompanyName, ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])
    FROM tblCompany;
    Paul

  9. #9
    Join Date
    Aug 2010
    Posts
    87
    Paul,

    LOL, That's what I can't figure out. I read the argument descriptions and don't understand it.


    That's my challenge. I read the doc and I don't understand it.
    I have two columns, "sku" and "models" in "oldtable" and need them to be in "newtable".

    This is what I think it would be. I added "models" and a comma.

    Does this appear correct?

    INSERT INTO NewTable (SKU, Models)
    SELECT DISTINCT SKU, ConcatRelated("models" & ",")
    FROM OldTable

  10. #10
    Join Date
    Aug 2010
    Posts
    87
    This is the actual SQL code I'm using with the correct table and column names.

    If I try to view the Datasheet view in an update query, make table query or append query, they all return the error "Undefined Function "ConcatRelated" in Expression".


    INSERT INTO RESTRICTIONS (sku, restrictions)
    SELECT DISTINCT TONERFINDERMATCHBOOK.sku, ConcatRelated("restrictions" & ",")
    FROM TONERFINDERMATCHBOOK, RESTRICTIONS;

  11. #11
    Join Date
    Aug 2010
    Posts
    87
    It appears that I need to add a module / function?

    Anyone know how to add a module / function based on this code?

  12. #12
    Join Date
    Aug 2010
    Posts
    87
    I figured out (i think) how to add the module. And I still get the error.

    Code

    INSERT INTO RESTRICTIONS (sku, restrictions)
    SELECT DISTINCT TONERFINDERMATCHBOOK.sku, ConcatRelated("restrictions" & ",")
    FROM TONERFINDERMATCHBOOK, RESTRICTIONS;

  13. #13
    Join Date
    Aug 2010
    Posts
    87
    Does anyone know what to do? I don't mind starting over! I have the Module in, I just don't even know if my code is correct.

  14. #14
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Does these look similar?

    ConcatRelated("restrictions" & ",")
    ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])

    Try

    ConcatRelated("restrictions", "TONERFINDERMATCHBOOK", "sku = '" & [sku] & "'")
    Paul

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    And for starters just try to get the query returning the correct values:

    SELECT DISTINCT TONERFINDERMATCHBOOK.sku, ConcatRelated("restrictions", "TONERFINDERMATCHBOOK", "sku = '" & [sku] & "'") As ConcatRestrictions
    FROM TONERFINDERMATCHBOOK
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •