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

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

[mallegirl]

Not sure if i am posting this in the right area. I have used the above code for my own db and it worked beautifully for 3 months.( Run it once a month) But all of a sudden i am getting an error message "Reserved error (-1038): there is no message for this error" could it be the actual data i am trying to use?

[NeoPa]

I don't think anyone discovered the solution to this problem (See post #19) MalleGirl.

I would try a Compact/Repair of your database first. I suspect database or other computer problems are involved (if as you say it's been working for three months without issue).

Generally, Last() is not the same as Max(), but I seem to have decided earlier (See post #25) that in this case it should be reliable (sometimes results can appear the same due to coincidence, a common one being the test data is in the originally expected order).

I'm not a great fan of jumping into work-arounds myself, before trying hard to find and resolve the issue. If it proves unsolvable, then it may be the only solution. Otherwise, what you learn solving problems invariable stands you in good stead for later issues.

Anyway, Good luck with your problem and welcome to Bytes!

[Whizzo]

Hmm... Last() worked for me too, when Max() threw up that Reserved Error. How strange!

[Ricksta]

I have had a similar problem when working with the max(concat()) function, but have not found an automated solution. The problem seems to be that there are characters being concatenated that are longer than the field will allow (max 255). A user has to go and export the data from my table to excel and manually concatenate the data using formulas then break up the concatenated values into two different cells when pasting back into Access.

I would love to find a solution to this problem as well... I think for now I am going to have to take a specific length (len()) of the first half of the concatenated results (using a left() function) and put that part in one cell and then have the right half (using a right function) go into another cell I specify where the length of the concatenated values is greater than the cell will allow... That's all I have for now...

[NeoPa]

If you find that there is a limit of 255 chars in your variable, then try changing line #13 of the code in
Combining Rows-Opposite of Union to :

    Concat = Left(strItems, 255)

I'm afraid this is not the sort of thing I would ever do in a database, and I would NEVER recommend doing it as it breaks all the rules of database normalisation (See Normalisation and Table structures) as far as I see it, but as so many seem to want to, this is a bodge to allow it.

[Ricksta]

What would you suggest I do? Since my values are too long to fit into one cell I decided to split them between two cells on the same record. I still only have one PK... Thanks for the reply.

[NeoPa]

I would suggest firstly posting a question. You may PM me directly with a link to it if you like (normally against the rules).

When I have all the information I need I will look into it further. I can't promise a satisfactory solution, but I will look at it at least.

[kyjabber]

NeoPa - the 255 character limit is what caused my truncation problems. For this project, the excel macro is how I finally was able to complete the job.

Thankfully (I guess?), I haven't had as much of a complex need for data management. Thanks again for all the help a couple of years ago and it looks like this may have help a few others over time!

[NeoPa]

Wow!

A familiar name, but haven't seen you around much lately (alright - not for over two years :D).

You're right, this is a subject often requested (hence Kyjabber so familiar), and actually, a Relational Database system isn't the best place to handle it (I may have mentioned before), but we do our best to help, and you've done it in Excel eventually anyway. Possibly the most natural solution :)

Good to see you back, and thanks for posting your solution.

[Ricksta]

Thanks guys. I appreciate your help with this. I will continue to work with Excel.

[NeoPa]

A new post was added here but it has been moved into its own thread (Produce List of Items).

[NeoPa]

A new post was added here but it has been moved into its own thread (Combining Rows - Sort Order ).

[turtlkky]

Here is a modification to the function that would prevent multiple instances of the same category/franchise from displaying over and over again in one field entry. I was running into a problem if I wanted to seperate each record if other fields did not match in a table. Here is what my table looked like before making the changes. (Note, the Franchise column field was setup in my query as 'Franchises: Max(Concat([Node],[Franchise]))' Expression)


Field Names:
SiteID|Company|Division|Franchises|Nodes|BillTypes |HomesPassed|Unserviceable|DevelopmentIDs
----------------------------------------------------------
308 | 36 | 7 | 502 | JBZZZ | M | 3661 | N | _
----------------------------------------------------------
308 | 36 | 7 | 502, 502 | JBZZZ | M | 3661 | JBCSY
----------------------------------------------------------
308 | 36 | 7 | 502, 502, 502, 503, 504, 505, 506, 507, 508, 509 | JBZZZ | S | 3661 | N | _

**Notice the multiple instances of 502. So in order to remove the multiple instances I modified the function as follows...

'Concat Returns lists of items which are within a grouped field
Public Function Concat(strNode As String, strFranchise As String) As String
    Static strLastNode As String
    Static strFranchises As String
    Static strLastFranchise As String
 
    If (strNode = strLastNode And strFranchise <> strLastFranchise) Then
' orig = strFranchises = strFranchises & ", " & strFranchise
         strFranchises = strFranchises & ", " & strFranchise
    Else
' orig = strLastNode = strNode
' orig = strFranchises = strFranchise
         strLastNode = strNode
         strLastFranchise = strFranchise
         strFranchises = strFranchise
    End If
    Concat = strFranchises
 
End Function
 

And then afterwards (notice the Franchises are not duplicated in a single field now):
----------------------------------------------------------
308 | 36 | 7 | 502 | JBZZZ | M | 3661 | N | _
----------------------------------------------------------
308 | 36 | 7 | 502 | JBZZZ | M | 3661 | JBCSY
----------------------------------------------------------
308 | 36 | 7 | 502, 503, 504, 505, 506, 507, 508, 509 | JBZZZ | S | 3661 | N | _

[NeoPa]

Thanks for the suggestion, but I'll take a pass on that one. There is no need for the function to be modified (in fact it's not a good idea to).

In your case the source of the data should be restricted to avoid duplicates. It's very straightforward in the SQL. It's likely to cause complications if done in the function code.

I couldn't check your amendments in this case as the code as posted seemed to have no obvious changes.

PS. That is unless you simply removed the line :

strLastFranchise = strFranchise

In that case of course, there is clearly some lack of understanding as to how the code works.

[ramyooooo]

I got a solution for Reserved error (-1038)
but first i have to thank NeoPa and all group person

i made this change in your code

Public Function Concat(strIOSC As String, _
                       strFeature As String) As String
    Dim H as integer
    Static strLastIOSC As String
    Static strFeatures As String
 
    H = Len(strFeature)
    If Left(strFeatures , H) = Left(strFeature, H) Then
       strLastIOSC = ""
    End If
 
    If strIOSC = strLastIOSC Then
        strFeatures = strFeatures & ", " & strFeature
    Else
        strLastIOSC = strIOSC
        strFeatures = strFeature
    End If
    Concat = strFeatures
End Function

and if your data bigger than 255 use

Concat = Left(strFeatures, 255)

the middle code is to prevent duplicated data

thanks a lot all of you

[NeoPa]

Thank you for your suggestion - but it doesn't work. It doesn't do what the function is supposed to do accurately.

May I suggest in future, and this goes for anyone ever posting code, that you test it before posting. Air code - where you type it directly into the browser, so it's never been taken from the VBA IDE, is never a good idea.

[resullivan]

I know the OP is old, but this is something I am about to tackle. I want to automate this in Access to create a text field that will act as a narrative. I just wanted to add quickly that this is rather simple to do in excel without the need of VBA. Sort your data by the field you want to combine (in this example CompanyName). Let's assume the is in column A, Category column B, and Product column C. In the last two columns you will need these two formulas:

=if(and(A2=A1,B2=B1),D1 & ", " & C2, C2)

and

=if(and(A2=A3,B2=B3),"X","")

You then copy and paste special values the last two rows, and delete all rows with an "X" in the last column.