Combining Multiple Rows of one Field into One Result

[NeoPa]

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

A new post!
Can you post the SQL you used as well as the actual code (if different at all from my posted code).

[kyjabber]

Note, these are not working... and I can't figure out how I got them to work that one time.

LOL at teh expert... I just wish it sunk in a little easier. My IT dept gets intimated by what I do know (they know if I call I'm in a tough spot but they call me for help... WTF gives, ya know?). and it's frustrating b/c my director is approving me to go to classes (and wants me to learn this stuff) but somehow the IT director can veto that approval /banter

Module:

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

SQL Query

SELECT [CompanyName], [Category], Max(Concat([CompanyName] & [Category],[Product])) AS Products
FROM t_CompanyCategoriesProducts
GROUP BY [CompanyName], [Category];

And the first few records from the Table I'm calling from:

CompanyName	Category	Product
"R"" Farm 	Vegetables	Pumpkins
4-H Nursery	Vegetables	Tomatoes
A Winery & Restaurant	Fruits	Grapes
A Winery & Restaurant	Vegetables	Beans
A Winery & Restaurant	Vegetables	Beets
A Winery & Restaurant	Vegetables	Broccoli
A Winery & Restaurant	Vegetables	Brussel Sprouts
A Winery & Restaurant	Vegetables	Cabbage
Ay Farm	Vegetables	Beans
Ay Farm	Vegetables	Cucumber
Ay Farm	Vegetables	Green Onions
Ay Farm	Vegetables	Okra
Ay Farm	Vegetables	Peppers
Ay Farm	Vegetables	Rhubarb
Ay Farm	Vegetables	Sweet Corn
Aly Farm	Vegetables	Tomatoes
Aly Farms	Fruits	Melons
Aly Farms	Fruits	Watermelons
Aly Farms	Meats	Beef
Aly Farms	Vegetables	Beans
Aly Farms	Vegetables	Cucumber
Aly Farms	Vegetables	Eggplant
Aly Farms	Vegetables	Lettuce
Aly Farms	Vegetables	Okra
Aly Farms	Vegetables	Peppers

[NeoPa]

I'm sorry, but can you post the output from the data you've shown.
Everything looks fine to me atm. What exactly does go wrong?

[kyjabber]

Correct me if I'm wrong, but I should just have to run the query?

The message that comes up when I go to run the query is

"Reserved error (-1038); there is no message for this error."

Just so you know, I'm having some other computer problems today, that shouldn't be related, my video card drivers are X( corrupted or card is dying. Working to rectify that (it rains it pours. LOL) since my computer intermittently just decides to reboot.

I have pseudo Murphy's Law, if it can happen, it will happen. My other half has rubbed on to my good karma. :P

ETA: I can always email you the file, if you like.... :| I know it'll go through gmail as one of my IRL friends was helping, too.

[NeoPa]

Correct me if I'm wrong, but I should just have to run the query?

The message that comes up when I go to run the query is

"Reserved error (-1038); there is no message for this error."

Just so you know, I'm having some other computer problems today, that shouldn't be related, my video card drivers are X( corrupted or card is dying. Working to rectify that (it rains it pours. LOL) since my computer intermittently just decides to reboot.

I have pseudo Murphy's Law, if it can happen, it will happen. My other half has rubbed on to my good karma. :P

That certainly looks like you have computer problems :(
Revisit this when you have resolved them and are happy your system is working normally. Otherwise there's no good reason to assume you have any error in your database at all.

ETA: I can always email you the file, if you like.... :| I know it'll go through gmail as one of my IRL friends was helping, too.

I prefer not to use my e-mail in such public places - I'm already more than overloaded with spam on my domain :(
If we get to that stage (certainly after posting results to match your data) then we'll try attaching it to a post.

[kyjabber]

That certainly looks like you have computer problems :(
Revisit this when you have resolved them and are happy your system is working normally. Otherwise there's no good reason to assume you have any error in your database at all.

Thank you! I will keep trudging away!!!! :)

I prefer not to use my e-mail in such public places - I'm already more than overloaded with spam on my domain :(
If we get to that stage (certainly after posting results to match your data) then we'll try attaching it to a post.

I here you there.... I'm going to test and see if my system is back stable (it wasn't doing all this when I started LOL)... if not try it tomorrow on my work system.

Thank you again!!!!! And I'll report back once I get to work on it again. I know I'm the only one that's looked for this and hopefully my experience can help someone else.

[NeoPa]

It has already.
I updated the other thread you referred to already with the more general code :)

[kyjabber]

A buddy of mine took a look at the SQL (with database in hand)

He came up with this:

SELECT t_CompanyCategoriesProducts.CompanyName, t_CompanyCategoriesProducts.Category, Last(Concat([CompanyName] & [Category],[Product])) AS Products
FROM t_CompanyCategoriesProducts
GROUP BY t_CompanyCategoriesProducts.CompanyName, t_CompanyCategoriesProducts.Category;

And it worked.... computer at home isn't still, but that at least is. *wipes sweat from brow*

[kyjabber]

Except, now..... not all things in the list are coming up. Field size limitations? It looses some of the list. :|

[NeoPa]

A buddy of mine took a look at the SQL (with database in hand)

He came up with this:

SELECT t_CompanyCategoriesProducts.CompanyName, t_CompanyCategoriesProducts.Category, Last(Concat([CompanyName] & [Category],[Product])) AS Products
FROM t_CompanyCategoriesProducts
GROUP BY t_CompanyCategoriesProducts.CompanyName, t_CompanyCategoriesProducts.Category;

And it worked.... computer at home isn't still, but that at least is. *wipes sweat from brow*

As this is essentially the same except for using the Last() aggregate function instead of the Max() one, I can't see why one would work and not the other. Generally I avoid using Last() as the order that records come in is often unpredictable and depends on Access optimisations. In this case, however, it doesn't matter as the last one is, by definition, the one required. Regardless of the incoming order. So this solution is good.

[NeoPa]

Except, now..... not all things in the list are coming up. Field size limitations? It looses some of the list. :|

As far as your problem goes, it would be interesting to see where it loses it from. Is it whole entries? Or is it lost after a certain number of characters?

[kyjabber]

As far as your problem goes, it would be interesting to see where it loses it from. Is it whole entries? Or is it lost after a certain number of characters?

Not sure, I *think* it's characters, but I haven't looked at that.

My friend (since he knows actually what he's doing at this point instead of me muddling) did some other changes to do the list like I was talking about. I've got to go look at all that (just got the email). I'll post that, too.

If you want on the SQL part, i can post what he said as to why with the "Last" worked.... I haven't had time to digest it all yet!

[NeoPa]

Not sure, I *think* it's characters, but I haven't looked at that.

As always, we need the info before we can help.

My friend (since he knows actually what he's doing at this point instead of me muddling) did some other changes to do the list like I was talking about. I've got to go look at all that (just got the email). I'll post that, too.

I'm wondering if it's a good idea to have us involved if your friend is running with the problem. We're more than happy to help but if you're getting instructions from somewhere else at the same time then wires could even more easily get crossed. This is particularly true if we're not kept informed of any changes.

If you want on the SQL part, i can post what he said as to why with the "Last" worked.... I haven't had time to digest it all yet!

That shouldn't be necessary, unless his reasoning is different from that outlined in Post #25.

Have fun and good luck -NeoPa.

[kyjabber]

Thank you.... I appreciate all the help! I'll probably post any other solutions, if that's fine, b/c I like to keep stuff in the same place and since I had a hard time googling any solutions, I'd like to share them.

[NeoPa]

That's certainly fine :)
We encourage posters to post resolutions when found - even if found somewhere other than The Scripts.
That way The Scripts becomes a better place for everyone who's looking for solutions - member or public.