Combining Multiple Rows of one Field into One Result

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • kyjabber
    New Member
    • Jan 2007
    • 15

    Combining Multiple Rows of one Field into One Result

    I have a multi relationship database and I'm pulling the company's contact info, queryied if they are a grower, and a resulting list of their products by catgeory. I need to have the products listed all together in one field.

    Example:

    Code:
    CompanyName		Grower	Category	Product
    B Orchard			-1	Fruits		Apples
    B Orchard			-1	Fruits		Blackberries
    B Orchard			-1	Vegetables	Beans
    B Orchard			-1	Vegetables	Other
    B Orchard			-1	Vegetables	Peas
    B Orchard & Roadside Market -1	Fruits		Apples
    B Orchard & Roadside Market -1	Fruits		Blackberries
    B Orchard & Roadside Market -1	Vegetables	Beans
    B Orchard & Roadside Market -1	Vegetables	Beets
    B Orchard & Roadside Market -1	Vegetables	Broccoli
    B Orchard & Roadside Market -1	Vegetables	Peppers
    B Star Acres		-1	Vegetables	Asparagus
    B Star Acres		-1	Vegetables	Beans
    B Star Acres		-1	Vegetables	Beets
    B Farms			-1	Fruits		Blackberries
    B Farms			-1	Fruits		Melons
    B Farms			-1	Fruits		Watermelons
    B Farms			-1	Vegetables	Beans
    B Farms			-1	Vegetables	Broccoli
    B Farms			-1	Vegetables	Cabbage

    Desired output:
    Code:
    CompanyName		Grower Category Product, Product, Product
    B Orchard			-1	Fruits Apples, Blackberries
    B Orchard			-1	Vegetables Beans, Other, Peas
    B Orchard & Roadside Market -1	Fruits Apples, Blackberries
    B Orchard & Roadside Market -1	Vegetables Beans, Beets, Broccoli, Peppers
    B Star Acres		-1	Vegetables Asparagus, Beans, Beets
    B Farms			-1	Fruits Blackberries, Melons, Watermelons
    B Farms			-1	Vegetables Beans, Broccoli, Cabbage

    Even Better if it made a new column for the different Categories/corresponding product, but beggars can't be choosers, I'll go for the first chance, at least.

    Utopia Output:
    Code:
    CompanyName		Grower	Category Product(s)	Category Product(s)
    B Orchard			-1	Fruits Apples, Blackberries Vegetables Beans, Other, Peas
    B Orchard & Roadside Market -1	Fruits Apples, Blackberries Vegetables	Beans, Beets, Broccoli, Peppers
    B Star Acres		-1	Vegetables Asparagus, Beans, Beets
    B Farms			-1	Fruits	Blackberries, Melons, Watermelons Vegetables Beans, Broccoli, Cabbage
    I'm using access 2002 on xp.
    Last edited by kyjabber; Jan 18 '07, 05:38 PM. Reason: added platform
  • cyberdwarf
    Recognized Expert New Member
    • Nov 2006
    • 218

    #2
    Hi,

    You could try using an adodb recordset and looping thru, concatenating the products for each grower as you go

    Is this for display purposes/export to Excel?

    HTH

    Steve

    Comment

    • kyjabber
      New Member
      • Jan 2007
      • 15

      #3
      Originally posted by cyberdwarf
      Hi,

      You could try using an adodb recordset and looping thru, concatenating the products for each grower as you go

      Is this for display purposes/export to Excel?

      HTH

      Steve
      Yes, this is for display purposes/export to Excel. I'm having to generate this for another function to a contracter. My other option was to hand merge them in Excel with a macro.

      Unforunately my coding is very rusty, and I'm not very experienced in SQL or VBA. (Want to learn but manager won't send me to training :|, well he woudl but our IT staff won't approve.long story.)

      Comment

      • cyberdwarf
        Recognized Expert New Member
        • Nov 2006
        • 218

        #4
        If you are competent in Excel, then that will be the quickest solution.

        Otherwise you have a sharp learning-curve and not much time!

        Steve

        Comment

        • kyjabber
          New Member
          • Jan 2007
          • 15

          #5
          Originally posted by cyberdwarf
          If you are competent in Excel, then that will be the quickest solution.

          Otherwise you have a sharp learning-curve and not much time!

          Steve
          We'll... I've never been afraid to learn. I've seen several VBA and SQL scripts accomplishing something and I'm trying them out, but no luck so far.

          The excel Macro is annoying b/c I have to select each section and I have over 350 companies to go through and 3K products collectively.

          Thanks away, but I'll take any suggestions that I can get.

          ~Anna

          Comment

          • NeoPa
            Recognized Expert Moderator MVP
            • Oct 2006
            • 32645

            #6
            Excel may be easier (with formulas rather than bit-by-bit) but in Access you could look at a Cross-Tab Query.

            Comment

            • kyjabber
              New Member
              • Jan 2007
              • 15

              #7
              Originally posted by NeoPa
              Excel may be easier (with formulas rather than bit-by-bit) but in Access you could look at a Cross-Tab Query.
              How would you suggest with formulas? each Company has a different of products and category mixes and a number of records?

              What I posted was an exert of a query that does contain data from three different tables (one is a pass through table that give the product its ID).

              I can post an excel file that shows the results of the query and what I've done by hand. I just *know* there has to be an easier (and quicker way) to do this and unfortunately even though I have a concept of what can be done I lack the skills to do that on my own (but I'm really liking the topics in this forum so hopefully over time I can keep improving my skills.

              I saw this thread: Combining Rows-Opposite of Union, and thought it was similar, but I haven't figured out the syntax yet for my own fields (and I'm really going at this blind. srsly, I know enough to be dangerous, but I do understand the concepts). and wasn't sure how to do the stings for three fields/columns.
              Last edited by NeoPa; Apr 4 '08, 01:09 PM. Reason: HyperLink

              Comment

              • NeoPa
                Recognized Expert Moderator MVP
                • Oct 2006
                • 32645

                #8
                I'd forgotten I'd already posted that solution in code.
                In that case, you're better off using a derivative of that.
                Let me know where you got stuck.

                Comment

                • NeoPa
                  Recognized Expert Moderator MVP
                  • Oct 2006
                  • 32645

                  #9
                  I can possibly go through the Excel one later when less busy - but thinking about it - it is more complicated than I thought to explain :(

                  Comment

                  • kyjabber
                    New Member
                    • Jan 2007
                    • 15

                    #10
                    I'm not sure how to post attachments. Sorry to be such a n00b!

                    Here's what I got in the module thus far, but I know I don't all three fields represented and I hadn't figured it out yet (was looking for examples and reading to know what I was doing... )

                    I don't know what to do with the field Category yet.
                    Code:
                    Option Compare Database
                    'Concat Returns Products
                    'listed by company
                    Public Function Concat(strCompanyName As String, _
                                           strProduct As String) As String
                        Static strLastCompanyName As String
                        Static strProducts As String
                    
                        If strCompanyName = strLastCompanyName Then
                            strProducts = strProducts & ", " & strProduct
                        Else
                            strLastCompanyName = strCompanyName
                            strProducts = strProduct
                        End If
                        Concat = strProducts
                    End Function
                    And here's what I put in the SQL Query
                    Once again, I need to add the Catgeory field for it to select. (and once I know how to add additional fields to this then maybe I can add the FNAME, LNAME, addy fields? or I'll just c/p into the excel sheet I have to have.

                    Code:
                    SELECT CompanyName, Max(Concat(CompanyName,Product)) AS Products
                    FROM t_CompanyCategoriesProducts
                    GROUP BY CompanyName;
                    Oh and to possibly make life simpler for me, I c/p the query results into a new Table (t_CompanyCateg oriesProducts), so that I'll have a static set to work with to get this going (and hopefully simplify in the mean while. And that table only has those three fields.

                    Thank you again, so much. I feel like I'm making progress. :)

                    Comment

                    • NeoPa
                      Recognized Expert Moderator MVP
                      • Oct 2006
                      • 32645

                      #11
                      You don't give me the impression of being a n00b.
                      You've converted the other code to your requirements perfectly it seems to me :)
                      Anyway, I had a better idea about the code which may help people to understand its flexibility better :
                      Code:
                      'Concat Returns lists of items which are within a grouped field
                      Public Function Concat(strGroup As String, _
                                             strItem As String) As String
                          Static strLastGroup As String
                          Static strItems As String
                      
                          If strGroup = strLastGroup Then
                              strItems = strItems & ", " & strItem
                          Else
                              strLastGroup = strGroup
                              strItems = strItem
                          End If
                          Concat = strItems
                      End Function
                      The calling code (SQL) is exactly the same.

                      **Edit** To have available as part of Best Answer (Combining Rows-Opposite of Union).

                      Comment

                      • NeoPa
                        Recognized Expert Moderator MVP
                        • Oct 2006
                        • 32645

                        #12
                        Reread (and edited for clarity) original post and now understand a little better.
                        Your SQL wasn't as right as I previously thought. Try :
                        Code:
                        SELECT CompanyName,
                               Category,
                               Max(Concat([CompanyName] & [Category],[Product])) AS Products
                        FROM t_CompanyCategoriesProducts
                        GROUP BY CompanyName,Category;
                        The []s are probably unnecessary but they help the reader when mixing it up with VBA sometimes.
                        When you have this query to your liking, you can consider running the results through a Cross-Tab Query and see if you manage to get your utopian output.
                        Let us know how you get on.

                        Comment

                        • kyjabber
                          New Member
                          • Jan 2007
                          • 15

                          #13
                          Awww, thanks NeoPa! You rock.

                          Yesterday, I added the (that I found someplace on the web and fixed for my needs) Macro below to Excel:
                          Code:
                          Sub Combine()
                              Dim J As Integer
                          
                              If Selection.Cells.Count > 1 Then
                                  For J = 2 To Selection.Cells.Count
                                      Selection.Cells(1).Value = _
                                        Selection.Cells(1).Value & ", " & _
                                        Selection.Cells(J).Value
                                      Selection.Cells(J).Clear
                                  Next J
                              End If
                          End Sub
                          For good measure (since this was given as one solution to this task), to add this in excel got to Tools -> Macro, add COMBINE to the top field, then click Create. Copy/Paste into there. To add a shortcut key, go back to the Tools - Macro Menu and click "Options".

                          With this, you still have to delete the rows by hand. Short cut for that is Ctrl+-. If the whole row is selected, it'll delete the whole row. If not, it'll popup the menu, click "R" and then enter for the whole row.

                          You then select the cells (ways to do that sans mouse are using Shift for highlight and the up/down or left/right arrow keys to highlight the area. Shift+Ctrl and the arrow key selects a larger group for FYI) and hit the short cut key to run the macro.

                          One thing to note is that you can't undo with this macro. And remember to save often! There's a simliar piece of shareware that you can buy that puts this on your menu. Don't waste your money. This macro was cleaner and easier than that menu... one you couldn't do the shortcut and two, this macro method was much much quicker to use.

                          I finished doing this with the spreadsheet last night (well, 2 am this morning) (the excel method), but I'm stubborn and I'lll work on this some more in Access!

                          Comment

                          • kyjabber
                            New Member
                            • Jan 2007
                            • 15

                            #14
                            With the SQL as you have listed somehow I got it to run once, (but now I"m getting an error, of course I'm doing this at home and it's Access 2000 on XP rather than Access 2002, but really shouldn't matter)

                            When it did run, the products where not combined, but it did only list a category per CompanyName (Say a CompanyName has fruits, vegetables, and organics it listed:

                            Code:
                            CompanyName         Category      Product
                            CompanyName1       Fruit             Pears
                            CompanyName1       Vegetables   Cabbage
                            CompanyName2       Organics       Eggs
                            CompanyName3       Vegetables    Beets
                            CompanyName3       Meats           Turkey
                            But say CompanyName1 should have had at least more than one fruit or vegetable listed. :(

                            I still say that in the Module field Category should be represented and I'm not sure how that should be....

                            Comment

                            • NeoPa
                              Recognized Expert Moderator MVP
                              • Oct 2006
                              • 32645

                              #15
                              Originally posted by kyjabber
                              Awww, thanks NeoPa! You rock.

                              Yesterday, I added the (that I found someplace on the web and fixed for my needs) Macro below to Excel:
                              Code:
                              Sub Combine()
                                  Dim J As Integer
                              
                                  If Selection.Cells.Count > 1 Then
                                      For J = 2 To Selection.Cells.Count
                                          Selection.Cells(1).Value = _
                                            Selection.Cells(1).Value & ", " & _
                                            Selection.Cells(J).Value
                                          Selection.Cells(J).Clear
                                      Next J
                                  End If
                              End Sub
                              For good measure (since this was given as one solution to this task), to add this in excel got to Tools -> Macro, add COMBINE to the top field, then click Create. Copy/Paste into there. To add a shortcut key, go back to the Tools - Macro Menu and click "Options".

                              With this, you still have to delete the rows by hand. Short cut for that is Ctrl+-. If the whole row is selected, it'll delete the whole row. If not, it'll popup the menu, click "R" and then enter for the whole row.

                              You then select the cells (ways to do that sans mouse are using Shift for highlight and the up/down or left/right arrow keys to highlight the area. Shift+Ctrl and the arrow key selects a larger group for FYI) and hit the short cut key to run the macro.

                              One thing to note is that you can't undo with this macro. And remember to save often! There's a simliar piece of shareware that you can buy that puts this on your menu. Don't waste your money. This macro was cleaner and easier than that menu... one you couldn't do the shortcut and two, this macro method was much much quicker to use.

                              I finished doing this with the spreadsheet last night (well, 2 am this morning) (the excel method), but I'm stubborn and I'lll work on this some more in Access!
                              With that attitude you'll be an expert in here before long :)
                              My idea was slightly different though (and didn't require VBA).
                              Assume data of form :
                              Code:
                                    A       B          C                                C too
                              Row GroupID ItemToList Formula                           Result
                              1   A       Scotch     =$B1                              Scotch
                              2   A       Beer       =IF($A2=$A1,$C1 & ", ","") & $B2  Scotch, Beer
                              3   A       Wine       Drag from C2                      Scotch, Beer, Wine
                              4   B       Scoth      Drag from C2                      Scotch
                              5   B       Beer       Drag from C2                      Scotch, Beer
                              6   C       Wine       Drag from C2                      Wine
                              When you've dragged down the data from C2 the C column has the value required in the last row for each GroupID. At this point I would use Copy, followed by Paste Special (Values) to replace the formulas in column C with their data equivalents.
                              At this point you enter the formula in column D to find those rows which contain the full list.
                              Code:
                                    A       B         C                    D
                              Row GroupID ItemToList Data                Formula      Result
                              1   A       Scotch     Scotch              =($A1=$A2)   TRUE
                              2   A       Beer       Scotch, Beer        Drag from D1 TRUE
                              3   A       Wine       Scotch, Beer, Wine  Drag from D1 FALSE
                              4   B       Scotch     Scotch              Drag from D1 TRUE
                              5   B       Beer       Scotch, Beer        Drag from D1 FALSE
                              6   C       Wine       Wine                Drag from D1 FALSE
                              When this is done, again Copy / Paste Special (Values) for column D this time.
                              Sort the data by column D and you should find all the required lines are together and the other ones can easily be deleted as they are also grouped together.

                              Comment

                              Working...