Combining Multiple Rows of one Field into One Result

[kyjabber]

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:

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:

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:

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.

[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

[kyjabber]

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.)

[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

[kyjabber]

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

[NeoPa]

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

[kyjabber]

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.

[NeoPa]

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.

[NeoPa]

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 :(

[kyjabber]

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.

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.

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. :)

[NeoPa]

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 :

'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).

[NeoPa]

Reread (and edited for clarity) original post and now understand a little better.
Your SQL wasn't as right as I previously thought. Try :

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.

[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:

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!

[kyjabber]

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:

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....

[NeoPa]

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:

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 :

      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.

      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.