Combining Multiple Rows of one Field into One Result

[mallegirl]

ugh- Help with comb mult rows of one field into 1 result

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]

Similar issue

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|Franch ises|Nodes|Bill Types|HomesPass ed|Unserviceabl e|DevelopmentID s
----------------------------------------------------------
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]

thanks a lot

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
    [B]Dim H as integer[/B]
    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