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