By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,490 Members | 1,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,490 IT Pros & Developers. It's quick & easy.

Combining Multiple Rows of one Field into One Result

P: 15
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:

Expand|Select|Wrap|Line Numbers
  1. CompanyName        Grower    Category    Product
  2. B Orchard            -1    Fruits        Apples
  3. B Orchard            -1    Fruits        Blackberries
  4. B Orchard            -1    Vegetables    Beans
  5. B Orchard            -1    Vegetables    Other
  6. B Orchard            -1    Vegetables    Peas
  7. B Orchard & Roadside Market -1    Fruits        Apples
  8. B Orchard & Roadside Market -1    Fruits        Blackberries
  9. B Orchard & Roadside Market -1    Vegetables    Beans
  10. B Orchard & Roadside Market -1    Vegetables    Beets
  11. B Orchard & Roadside Market -1    Vegetables    Broccoli
  12. B Orchard & Roadside Market -1    Vegetables    Peppers
  13. B Star Acres        -1    Vegetables    Asparagus
  14. B Star Acres        -1    Vegetables    Beans
  15. B Star Acres        -1    Vegetables    Beets
  16. B Farms            -1    Fruits        Blackberries
  17. B Farms            -1    Fruits        Melons
  18. B Farms            -1    Fruits        Watermelons
  19. B Farms            -1    Vegetables    Beans
  20. B Farms            -1    Vegetables    Broccoli
  21. B Farms            -1    Vegetables    Cabbage

Desired output:
Expand|Select|Wrap|Line Numbers
  1. CompanyName        Grower Category Product, Product, Product
  2. B Orchard            -1    Fruits Apples, Blackberries
  3. B Orchard            -1    Vegetables Beans, Other, Peas
  4. B Orchard & Roadside Market -1    Fruits Apples, Blackberries
  5. B Orchard & Roadside Market -1    Vegetables Beans, Beets, Broccoli, Peppers
  6. B Star Acres        -1    Vegetables Asparagus, Beans, Beets
  7. B Farms            -1    Fruits Blackberries, Melons, Watermelons
  8. 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:
Expand|Select|Wrap|Line Numbers
  1. CompanyName        Grower    Category Product(s)    Category Product(s)
  2. B Orchard            -1    Fruits Apples, Blackberries Vegetables Beans, Other, Peas
  3. B Orchard & Roadside Market -1    Fruits Apples, Blackberries Vegetables    Beans, Beets, Broccoli, Peppers
  4. B Star Acres        -1    Vegetables Asparagus, Beans, Beets
  5. B Farms            -1    Fruits    Blackberries, Melons, Watermelons Vegetables Beans, Broccoli, Cabbage
I'm using access 2002 on xp.
Jan 18 '07 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. 'Concat Returns lists of items which are within a grouped field
  2. Public Function Concat(strGroup As String, _
  3.                        strItem As String) As String
  4.     Static strLastGroup As String
  5.     Static strItems As String
  6.  
  7.     If strGroup = strLastGroup Then
  8.         strItems = strItems & ", " & strItem
  9.     Else
  10.         strLastGroup = strGroup
  11.         strItems = strItem
  12.     End If
  13.     Concat = strItems
  14. End Function
The calling code (SQL) is exactly the same.

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

Share this Question
Share on Google+
46 Replies


Expert 100+
P: 218
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
Jan 18 '07 #2

P: 15
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.)
Jan 18 '07 #3

Expert 100+
P: 218
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
Jan 18 '07 #4

P: 15
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
Jan 18 '07 #5

NeoPa
Expert Mod 15k+
P: 31,476
Excel may be easier (with formulas rather than bit-by-bit) but in Access you could look at a Cross-Tab Query.
Jan 19 '07 #6

P: 15
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.
Jan 19 '07 #7

NeoPa
Expert Mod 15k+
P: 31,476
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.
Jan 19 '07 #8

NeoPa
Expert Mod 15k+
P: 31,476
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 :(
Jan 19 '07 #9

P: 15
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.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. 'Concat Returns Products
  3. 'listed by company
  4. Public Function Concat(strCompanyName As String, _
  5.                        strProduct As String) As String
  6.     Static strLastCompanyName As String
  7.     Static strProducts As String
  8.  
  9.     If strCompanyName = strLastCompanyName Then
  10.         strProducts = strProducts & ", " & strProduct
  11.     Else
  12.         strLastCompanyName = strCompanyName
  13.         strProducts = strProduct
  14.     End If
  15.     Concat = strProducts
  16. End Function
  17.  
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT CompanyName, Max(Concat(CompanyName,Product)) AS Products
  2. FROM t_CompanyCategoriesProducts
  3. 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. :)
Jan 19 '07 #10

NeoPa
Expert Mod 15k+
P: 31,476
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 :
Expand|Select|Wrap|Line Numbers
  1. 'Concat Returns lists of items which are within a grouped field
  2. Public Function Concat(strGroup As String, _
  3.                        strItem As String) As String
  4.     Static strLastGroup As String
  5.     Static strItems As String
  6.  
  7.     If strGroup = strLastGroup Then
  8.         strItems = strItems & ", " & strItem
  9.     Else
  10.         strLastGroup = strGroup
  11.         strItems = strItem
  12.     End If
  13.     Concat = strItems
  14. End Function
The calling code (SQL) is exactly the same.

**Edit** To have available as part of Best Answer (Combining Rows-Opposite of Union).
Jan 21 '07 #11

NeoPa
Expert Mod 15k+
P: 31,476
Reread (and edited for clarity) original post and now understand a little better.
Your SQL wasn't as right as I previously thought. Try :
Expand|Select|Wrap|Line Numbers
  1. SELECT CompanyName,
  2.        Category,
  3.        Max(Concat([CompanyName] & [Category],[Product])) AS Products
  4. FROM t_CompanyCategoriesProducts
  5. 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.
Jan 21 '07 #12

P: 15
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:
Expand|Select|Wrap|Line Numbers
  1. Sub Combine()
  2.     Dim J As Integer
  3.  
  4.     If Selection.Cells.Count > 1 Then
  5.         For J = 2 To Selection.Cells.Count
  6.             Selection.Cells(1).Value = _
  7.               Selection.Cells(1).Value & ", " & _
  8.               Selection.Cells(J).Value
  9.             Selection.Cells(J).Clear
  10.         Next J
  11.     End If
  12. End Sub
  13.  
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!
Jan 21 '07 #13

P: 15
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:

Expand|Select|Wrap|Line Numbers
  1. CompanyName         Category      Product
  2. CompanyName1       Fruit             Pears
  3. CompanyName1       Vegetables   Cabbage
  4. CompanyName2       Organics       Eggs
  5. CompanyName3       Vegetables    Beets
  6. CompanyName3       Meats           Turkey
  7.  
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....
Jan 21 '07 #14

NeoPa
Expert Mod 15k+
P: 31,476
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:
Expand|Select|Wrap|Line Numbers
  1. Sub Combine()
  2.     Dim J As Integer
  3.  
  4.     If Selection.Cells.Count > 1 Then
  5.         For J = 2 To Selection.Cells.Count
  6.             Selection.Cells(1).Value = _
  7.               Selection.Cells(1).Value & ", " & _
  8.               Selection.Cells(J).Value
  9.             Selection.Cells(J).Clear
  10.         Next J
  11.     End If
  12. End Sub
  13.  
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 :
Expand|Select|Wrap|Line Numbers
  1.       A       B          C                                C too
  2. Row GroupID ItemToList Formula                           Result
  3. 1   A       Scotch     =$B1                              Scotch
  4. 2   A       Beer       =IF($A2=$A1,$C1 & ", ","") & $B2  Scotch, Beer
  5. 3   A       Wine       Drag from C2                      Scotch, Beer, Wine
  6. 4   B       Scoth      Drag from C2                      Scotch
  7. 5   B       Beer       Drag from C2                      Scotch, Beer
  8. 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.
Expand|Select|Wrap|Line Numbers
  1.       A       B         C                    D
  2. Row GroupID ItemToList Data                Formula      Result
  3. 1   A       Scotch     Scotch              =($A1=$A2)   TRUE
  4. 2   A       Beer       Scotch, Beer        Drag from D1 TRUE
  5. 3   A       Wine       Scotch, Beer, Wine  Drag from D1 FALSE
  6. 4   B       Scotch     Scotch              Drag from D1 TRUE
  7. 5   B       Beer       Scotch, Beer        Drag from D1 FALSE
  8. 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.
Jan 21 '07 #15

NeoPa
Expert Mod 15k+
P: 31,476
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:

Expand|Select|Wrap|Line Numbers
  1. CompanyName         Category      Product
  2. CompanyName1       Fruit             Pears
  3. CompanyName1       Vegetables   Cabbage
  4. CompanyName2       Organics       Eggs
  5. CompanyName3       Vegetables    Beets
  6. CompanyName3       Meats           Turkey
  7.  
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).
Jan 21 '07 #16

P: 15
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. 'Concat Returns Products
  3. 'listed by company
  4. Public Function Concat(strCompanyName As String, _
  5.                        strProduct As String) As String
  6.     Static strLastCompanyName As String
  7.     Static strProducts As String
  8.  
  9.     If strCompanyName = strLastCompanyName Then
  10.         strProducts = strProducts & ", " & strProduct
  11.     Else
  12.         strLastCompanyName = strCompanyName
  13.         strProducts = strProduct
  14.     End If
  15.     Concat = strProducts
  16. End Function
  17.  
SQL Query
Expand|Select|Wrap|Line Numbers
  1. SELECT [CompanyName], [Category], Max(Concat([CompanyName] & [Category],[Product])) AS Products
  2. FROM t_CompanyCategoriesProducts
  3. GROUP BY [CompanyName], [Category];
  4.  
And the first few records from the Table I'm calling from:
Expand|Select|Wrap|Line Numbers
  1. CompanyName    Category    Product
  2. "R"" Farm     Vegetables    Pumpkins
  3. 4-H Nursery    Vegetables    Tomatoes
  4. A Winery & Restaurant    Fruits    Grapes
  5. A Winery & Restaurant    Vegetables    Beans
  6. A Winery & Restaurant    Vegetables    Beets
  7. A Winery & Restaurant    Vegetables    Broccoli
  8. A Winery & Restaurant    Vegetables    Brussel Sprouts
  9. A Winery & Restaurant    Vegetables    Cabbage
  10. Ay Farm    Vegetables    Beans
  11. Ay Farm    Vegetables    Cucumber
  12. Ay Farm    Vegetables    Green Onions
  13. Ay Farm    Vegetables    Okra
  14. Ay Farm    Vegetables    Peppers
  15. Ay Farm    Vegetables    Rhubarb
  16. Ay Farm    Vegetables    Sweet Corn
  17. Aly Farm    Vegetables    Tomatoes
  18. Aly Farms    Fruits    Melons
  19. Aly Farms    Fruits    Watermelons
  20. Aly Farms    Meats    Beef
  21. Aly Farms    Vegetables    Beans
  22. Aly Farms    Vegetables    Cucumber
  23. Aly Farms    Vegetables    Eggplant
  24. Aly Farms    Vegetables    Lettuce
  25. Aly Farms    Vegetables    Okra
  26. Aly Farms    Vegetables    Peppers
  27.  
Jan 21 '07 #17

NeoPa
Expert Mod 15k+
P: 31,476
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?
Jan 21 '07 #18

P: 15
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.
Jan 21 '07 #19

NeoPa
Expert Mod 15k+
P: 31,476
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.
Jan 21 '07 #20

P: 15
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.
Jan 21 '07 #21

NeoPa
Expert Mod 15k+
P: 31,476
It has already.
I updated the other thread you referred to already with the more general code :)
Jan 21 '07 #22

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

He came up with this:

Expand|Select|Wrap|Line Numbers
  1. SELECT t_CompanyCategoriesProducts.CompanyName, t_CompanyCategoriesProducts.Category, Last(Concat([CompanyName] & [Category],[Product])) AS Products
  2. FROM t_CompanyCategoriesProducts
  3. GROUP BY t_CompanyCategoriesProducts.CompanyName, t_CompanyCategoriesProducts.Category; 
  4.  
And it worked.... computer at home isn't still, but that at least is. *wipes sweat from brow*
Jan 22 '07 #23

P: 15
Except, now..... not all things in the list are coming up. Field size limitations? It looses some of the list. :|
Jan 22 '07 #24

NeoPa
Expert Mod 15k+
P: 31,476
A buddy of mine took a look at the SQL (with database in hand)

He came up with this:

Expand|Select|Wrap|Line Numbers
  1. SELECT t_CompanyCategoriesProducts.CompanyName, t_CompanyCategoriesProducts.Category, Last(Concat([CompanyName] & [Category],[Product])) AS Products
  2. FROM t_CompanyCategoriesProducts
  3. GROUP BY t_CompanyCategoriesProducts.CompanyName, t_CompanyCategoriesProducts.Category; 
  4.  
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.
Jan 23 '07 #25

NeoPa
Expert Mod 15k+
P: 31,476
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?
Jan 23 '07 #26

P: 15
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!
Jan 23 '07 #27

NeoPa
Expert Mod 15k+
P: 31,476
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.
Jan 23 '07 #28

P: 15
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.
Jan 23 '07 #29

NeoPa
Expert Mod 15k+
P: 31,476
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.
Jan 23 '07 #30

P: 4
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?
Dec 2 '08 #31

NeoPa
Expert Mod 15k+
P: 31,476
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!
Dec 3 '08 #32

P: 44
Hmm... Last() worked for me too, when Max() threw up that Reserved Error. How strange!
Mar 30 '09 #33

P: 3
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...
Apr 14 '09 #34

NeoPa
Expert Mod 15k+
P: 31,476
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 :
Expand|Select|Wrap|Line Numbers
  1.     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.
Apr 14 '09 #35

P: 3
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.
Apr 14 '09 #36

NeoPa
Expert Mod 15k+
P: 31,476
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.
Apr 15 '09 #37

P: 15
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!
Apr 27 '09 #38

NeoPa
Expert Mod 15k+
P: 31,476
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.
Apr 27 '09 #39

P: 3
Thanks guys. I appreciate your help with this. I will continue to work with Excel.
Apr 27 '09 #40

NeoPa
Expert Mod 15k+
P: 31,476
A new post was added here but it has been moved into its own thread (Produce List of Items).
Jan 24 '10 #41

NeoPa
Expert Mod 15k+
P: 31,476
A new post was added here but it has been moved into its own thread (Combining Rows - Sort Order ).
May 6 '10 #42

P: 1
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...

Expand|Select|Wrap|Line Numbers
  1. 'Concat Returns lists of items which are within a grouped field
  2. Public Function Concat(strNode As String, strFranchise As String) As String
  3.     Static strLastNode As String
  4.     Static strFranchises As String
  5.     Static strLastFranchise As String
  6.  
  7.     If (strNode = strLastNode And strFranchise <> strLastFranchise) Then
  8. ' orig = strFranchises = strFranchises & ", " & strFranchise
  9.          strFranchises = strFranchises & ", " & strFranchise
  10.     Else
  11. ' orig = strLastNode = strNode
  12. ' orig = strFranchises = strFranchise
  13.          strLastNode = strNode
  14.          strLastFranchise = strFranchise
  15.          strFranchises = strFranchise
  16.     End If
  17.     Concat = strFranchises
  18.  
  19. End Function
  20.  


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 | _
Jun 2 '10 #43

NeoPa
Expert Mod 15k+
P: 31,476
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 :
Expand|Select|Wrap|Line Numbers
  1. strLastFranchise = strFranchise
In that case of course, there is clearly some lack of understanding as to how the code works.
Jun 2 '10 #44

P: 1
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

Expand|Select|Wrap|Line Numbers
  1. Public Function Concat(strIOSC As String, _
  2.                        strFeature As String) As String
  3.     Dim H as integer
  4.     Static strLastIOSC As String
  5.     Static strFeatures As String
  6.  
  7.     H = Len(strFeature)
  8.     If Left(strFeatures , H) = Left(strFeature, H) Then
  9.        strLastIOSC = ""
  10.     End If
  11.  
  12.     If strIOSC = strLastIOSC Then
  13.         strFeatures = strFeatures & ", " & strFeature
  14.     Else
  15.         strLastIOSC = strIOSC
  16.         strFeatures = strFeature
  17.     End If
  18.     Concat = strFeatures
  19. End Function
and if your data bigger than 255 use

Expand|Select|Wrap|Line Numbers
  1. Concat = Left(strFeatures, 255)
the middle code is to prevent duplicated data

thanks a lot all of you
Apr 28 '15 #45

NeoPa
Expert Mod 15k+
P: 31,476
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.
May 6 '15 #46

P: 2
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:

Expand|Select|Wrap|Line Numbers
  1. =if(and(A2=A1,B2=B1),D1 & ", " & C2, C2)
and

Expand|Select|Wrap|Line Numbers
  1. =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.
Jun 25 '15 #47

Post your reply

Sign in to post your reply or Sign up for a free account.