Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    13

    Unanswered: concatenating & GroupBy

    Hello does anybody knows a simple SQL answer on the following question:
    presume the following table:
    reference.|.productID..|..description
    C1...........|.... 2211.....|....10F 15V
    C3...........|.... 2211.....|....10F 15V
    C6...........|.... 1392.....|....22F 10V
    R3...........|.... 0100.....|....10K
    R4...........|.... 0100.....|....10K
    R2...........|.... 0130.....|.... 4K7

    The result should be...

    productID..|.. references..|.. description..|.. amount
    2211.........|.. C1,C3........|.. 10F 15V....|.. 2
    1392.........|.. C6.............|.. 22F 10V....|.. 1
    0100.........|.. R3,R4........|.. 10K............|.. 2
    0130.........|.. R2.............|.. 4K7............|.. 1


    Seems to be an ordenairy SUM and GROUP BY query
    but the problem is the concatenation of the references.
    Im not sure this is possible with pure SQL
    Who has the solution?

    Thanks
    Robert

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Edacon,

    Read this last night and thought it might be an teresting exercise.

    I don't think this is posssible with SQL, but I'd love to have someone prove me wrong. I was able to do it with VBA. So, I whiped up a sample and wrote an article.

    Link to article - Concatenating unique field values for records matching a query's GROUP BY clause
    (That title is a mouthful! )

    Lemme know if that works for you. Also, my thanks to you for inspiring the article!

    Hope this helps!


    I am curious, why do you need to create this concatenated string?
    Last edited by Cosmos75; 01-14-06 at 12:38.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  3. #3
    Join Date
    Oct 2004
    Posts
    13

    many thanks

    Hi Cosmo,
    This is really a good solution.
    ALthough I am not familiar with VBA your article was clear enough to help me on the way.
    To answer your question:
    The reason why is simple, I'm a electronic designer and the references point to components on a printed circuit board.
    For cost calculations and a good overview of the desighn it is not handy to havee all components on seperste lines. You would rather have them summarized so that all the same components come on one line with the total number. But when you have to place the components you should know which component has which value and so on.
    this VBA solution helps me to have the best listing of my components.

    Best regards

    Robert (edadcon)

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Edadcon,

    Glad that the article helped! I also must thank you for asking a question that intrigued me enough to come up with that article!


    I also appreciate your posting back to let me know that it did help. I spend quite a bit of time on writing the articles to explain my code or tip (sometimes more time that it takes to whip up the sample code or file), so it is personally gratifying to know that the explanation was clear enough for someone in your situation.


    Cheers!
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

Posting Permissions

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