Concatenate values from the same field

Query13.txtI need to concatenate values from the same field in Access to read as one long string.  
For every account, our staff enters notes in our system and those notes are saved for posterity on a sql server that is the source of my query.  
I need to the most recent note entered by specific staff members for specific account numbers.  

Each note can consist of multiple lines in our system. This is what a single note looks like when entered in our system by employee 07676:


 And each line is stored as an individual record on the server.  Here is the same note , as stored on our sql server:


 
One note entered, three lines, three record in the same field that I need concatenated .

Every note entered by the same person, on the same day,for same account, is assigned a [NoteSequence] number which is the field the above three records have in common (in addtion to the account number, employee id, date).


I need to concatenate the values in the [NoteMessage] field  where [NoteSequence]=[NoteSequence]  order by [TransactionSequence]

I need to turn this :
IB-Inbound
Borrower Intent - Keep Property                  
RFD006 -  Curtailment of Income      

into this:
             
IB-Inbound .Borrower Intent - Keep Property.  RFD006 -  Curtailment of Income          

 
TransactionSequence is the order of each line item/record.
NoteSequence is the number assigned to each note made by the same employee on any given day, on the same account.
07676 actually made 3 notes on that day consisting of 9 lines.  
 

As it appears in ours system, order by NoteSequence 3, 2, 1
 
Each note entered may consist of multiple lines.  The above shows 3 different notes entered on the same day by employee 07676. The newest note is on top consists of three lines.
JoeMommasMommaAnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
You can select for XML and that concatenates rather nicely, and no, it doesnt leave it in XML format....

I will have a look at your data / samples and be back in a short while

Where is Account ?
1
PortletPaulEE Topic AdvisorCommented:
it would be useful to provide the same data seen on the second image as text.

Imagine a world where we provided SQL queries as images, would you enjoy the transcription?
0
PortletPaulEE Topic AdvisorCommented:
As Mark indicated you can use "for xml path" as a means to concatenate over multiple rows. In the example below I am only returning 2 columns but you can extend that, however you want to ensure that you only get one row for each wanted note. I am note certain how you intend to achieve "latest" but I guess you would use the date column. For this you may want to use dense_rank() over(partition by tellerid order by NoteSequence DESC)  then select the rows that have a value of 1 from that function.
SELECT DISTINCT
    t1.Tellerid
  , ca.Notes
FROM Table1 t1
    CROSS APPLY (
        SELECT
        STUFF((
              SELECT
                    ' ' + [NoteMessage]
              FROM Table1 t2
              WHERE t1.Tellerid = t2.Tellerid and t1.NoteSequence = t2.NoteSequence
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca (Notes)
;

Open in new window

CREATE TABLE Table1
    ([Tellerid] int, [NoteMessage] varchar(100), [TransactionSequence] int, [NoteSequence] int)
;
 
INSERT INTO Table1
    ([Tellerid], [NoteMessage], [TransactionSequence], [NoteSequence])
VALUES
    (123, 'IB-Inbound', 1, 3),
    (123, 'Borrower Intent - Keep Property', 2, 3),
    (123, 'RFD006 -  Curtailment of Income', 3, 3)
;

Open in new window

| Tellerid |                                                                      Notes |
|----------|----------------------------------------------------------------------------|
|      123 | IB-Inbound Borrower Intent - Keep Property RFD006 -  Curtailment of Income |

Open in new window

also see: http://sqlfiddle.com/#!18/48530/1

+ with selection for "latest"
SELECT DISTINCT
    t1.Tellerid
  , ca.Notes
FROM Table1 t1
    CROSS APPLY (
        SELECT
        STUFF((
              SELECT
                    ' ' + [NoteMessage]
              FROM (
                select *
                , dense_rank() over(partition by Tellerid
                                    order by NoteSequence DESC) as rn
                from Table1) t2
              WHERE t2.rn = 1
              AND t1.Tellerid = t2.Tellerid and t1.NoteSequence = t2.NoteSequence
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca (Notes)
;

Open in new window

0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

JoeMommasMommaAnalystAuthor Commented:
I can use xml path in an Access query?
0
PortletPaulEE Topic AdvisorCommented:
The syntax is unique to SQL Server.

Both "cross apply" and "for xml path" are T-SQL constructs.
0
PortletPaulEE Topic AdvisorCommented:
You need a VBA function in Access to concatenate over multiple rows.
0
Mark WillsTopic AdvisorCommented:
using a test table named #Notes (used twice) we can get
Select distinct [TellerID], [NoteArea], [TransactionDate], 
      stuff((select ',' + [NoteMessage] 
       from #Notes SN 
       where sn.tellerid = n.tellerid 
       and SN.NoteArea = N.NoteArea 
       and sn.TransactionDate = N.TransactionDate 
       and SN.NoteSequence = N.NoteSequence
       Order by [TransactionSequence]
	   FOR XML path('')),1,1,'') NOTES
from #Notes N

Open in new window

0
PatHartmanCommented:
You have a lot of SQL Server answers because of the topics you chose.  If you are not using pass-through queries, you probably want an Access solution.  Please clarify.
1
Mark WillsTopic AdvisorCommented:
*laughing* just saw the Access requirement. This definitely not Access / VBA

So why have Microsoft SQL Server as a Topic ?

Any way read : https://www.experts-exchange.com/articles/2380/Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

which has a function that you can drop in to do exactly that.
0
PortletPaulEE Topic AdvisorCommented:
Not only the topics: it is stated the table is in SQL Server

Here is a previously answered question (PAQ) on for "group_concat" in Access

https://www.experts-exchange.com/questions/27981331/Access-SQL-get-concatenation-from-a-field-of-depending-records.html
0
JoeMommasMommaAnalystAuthor Commented:
im querying a sql server is how that tag got added. but using stricty ms access for this.

a vba function is what's needed is what I have gathered.
0
PortletPaulEE Topic AdvisorCommented:
or a "pass through" query which allows you to use T-SQL syntax as it is executed on the server

e.g. see
https://support.microsoft.com/en-us/help/303968/how-to-create-an-sql-pass-through-query-in-access
1
PatHartmanCommented:
Not only the topics: it is stated the table is in SQL Server
That isn't relevant when using Access UNLESS the OP is using unbound forms and pass through queries or stored procedures.  Otherwise, it is Access SQL syntax NOT T-SQL and VBA all the way.
0
PortletPaulEE Topic AdvisorCommented:
That isn't relevant when using Access

:) I meant, that the statement indicated the data resides in SQL Server & hence the assumption that T-SQL was relevant. The question does not state that the query had to be run from Access, this occurs later.
2
Gustav BrockCIOCommented:
You can use this function:

Public Function ConcatenateRecords( _
  ByVal strSource As String, _
  ByVal lngKey As Long, _
  ByVal strField As String, _
  Optional ByVal strSeparator As String = ";") _
  As String

' Concatenates values from one field (strField) from all
' records in query strSource using parameter Value lngKey.
' Values are separated by strSeparator.
' Default output like:
' 34;56;34;67;234
'
' 1999-10-12. Cactus Data ApS, CPH

  Dim dbs         As DAO.Database
  Dim qdf         As DAO.QueryDef
  Dim rst         As DAO.Recordset
  Dim fld         As DAO.Field
    
  Dim booPluralis As Boolean
  Dim strFields   As String
  
  On Error GoTo Err_ConcatenateRecords
  
  Set dbs = CurrentDb()
  
  If Len(strSource) > 0 And Len(strField) > 0 Then
    Set qdf = dbs.QueryDefs(strSource)
    qdf.Parameters(0) = lngKey
    Set rst = qdf.OpenRecordset()
    Set fld = rst.Fields(strField)
    
    With rst
      While Not .EOF
        If booPluralis = True Then
          ' There is more than one record.
          ' Add separator.
          strFields = strFields & strSeparator
        End If
        strFields = strFields & Trim(fld.Value)
        booPluralis = True
        .MoveNext
      Wend
      .Close
    End With
    
    Set fld = Nothing
    Set rst = Nothing
    Set qdf = Nothing
  End If
  
  Set dbs = Nothing
  
  ConcatenateRecords = strFields
  
Exit_ConcatenateRecords:
  Exit Function
  
Err_ConcatenateRecords:
  MsgBox "Error " & Err.Number & ". " & Err.Description
  Resume Exit_ConcatenateRecords
  
End Function

Open in new window

Then a query like this:

PARAMETERS 
    [Key] Long;
SELECT 
    Notes.TransactionTime, 
    Notes.TransactionSequence, 
    Notes.TellerId, 
    Notes.NoteMessage
FROM 
    Notes
WHERE 
    (((Notes.TransactionTime)=[Key]\10000) AND ((Notes.TellerId)=[Key] Mod 10000))
ORDER BY 
    Notes.TransactionSequence;

Open in new window

Save it as NotesTimeTeller.

Finally, collect the pieces with this query:

SELECT 
    Notes.LoanNumber, 
    Notes.TransactionDate, 
    Notes.TransactionTime, 
    Notes.TransactionCode, 
    Notes.TellerId, 
    Notes.NoteArea, 
    Notes.CreateDate, 
    Notes.ModifiedDate, 
    ConcatenateRecords("NotesTimeTeller",[TransactionTime]*10000+[TellerId],"NoteMessage"," - ") AS NoteMessages
FROM 
    Notes
GROUP BY 
    Notes.LoanNumber, 
    Notes.TransactionDate, 
    Notes.TransactionTime, 
    Notes.TransactionCode, 
    Notes.TellerId, 
    Notes.NoteArea, 
    Notes.CreateDate, 
    Notes.ModifiedDate;

Open in new window

0
JoeMommasMommaAnalystAuthor Commented:
why is the [time]  field invloved? and the calculations using 10000?   i will try it but just to clarify we are trying to concatenate only the [notes] that have the same [notesequence], same [transactiondate], order by [transactionsequence]
0
Gustav BrockCIOCommented:
Those are to create a compound unique key.
0
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
JoeMommasMomma, below is a function I wrote years ago to do pretty much what you want;   it accepts arguments, builds a query based on those arguments, populates a recordset with the query results, and then loops through the recordset, concatenating results until the end of the recordset is reached.  Adding another optional argument to accept a SortBy value (TransactionSequence, in this specific instance) would be pretty simple, and I'll be happy to walk you through the process if you wish.  :)  

Public Function DConcatenate(Expr As String, Domain As String, Optional Criteria As String = vbNullString, _
    Optional Separator As String = ", ", Optional Distinct As Boolean = True, Optional Sort As String = "Asc", Optional Limit As Long = 0, Optional bolTrimLastDelim As Boolean = True) As String
'---------------------------------------------------------------------------------------
' Created   : 7/23/2009 09:15
' Author    : PCG
' Purpose   : compiles values from multiple rows into a single variable  --note that Expr may contain multiple fields and literals
' History   : based on http://www.vb123.com/kb/  Doug Steele's article "All in the Family"
'             20150320 PCG added code for optional sort of concatenated values,
'                 optional Select Distinct, and optional limit to number of values returned,
'                 using http://www.experts-exchange.com/Database/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html
'---------------------------------------------------------------------------------------
On Error GoTo HandleError

'Create recordset, SQL string to create the recordset, variable to use to hold the concatenated values:
      Dim rs As DAO.Recordset

'Create SQL string from variables Distinct, Expr, Domain, and Criteria:
    strSql = "SELECT " & IIf(Distinct, "DISTINCT ", "") & IIf(Limit > 0, "TOP " & Limit & " ", "") & Expr & " AS TheValue " & "FROM " & Domain
'If a criteria was passed, add it to the SQL string:
    If Len(Criteria) > 0 Then strSql = strSql & " WHERE " & Criteria
'If a sort was chosen, add it to the SQL string:
    strSql = strSql & Switch(Sort = "Asc", " ORDER BY " & Expr & " Asc", Sort = "Desc", "ORDER BY " & Expr & " Desc", Sort = "", "", True, "")

'open the recordset, loop through the records concatenate the data and the Separator, and remove the last Separator
    Set rs = CurrentDb().OpenRecordset(strSql)
      Do While rs.EOF = False
          DConcatenate = DConcatenate & rs!TheValue & Separator
          rs.MoveNext
      Loop
      
'Trim off last delimiter:
If bolTrimLastDelim Then
If Len(DConcatenate) > 0 Then DConcatenate = Left$(DConcatenate, Len(DConcatenate) - Len(Separator))
End If

'Clean up:
    rs.Close
    Set rs = Nothing

ExitFunction:
     Exit Function
HandleError:
     DoCmd.SetWarnings True
     MsgBox "DConcatenate Error " & Err.Number & " (" & Err.Description & ");  Line " & Erl
EndFunction:
    End Function

Open in new window

0
JoeMommasMommaAnalystAuthor Commented:
how do I specify the criteria?  
WHERE  [TransactionDate]= [TransactionDate] AND  [NoteSequence] = [NoteSequence] and  [I049A-ACCT] = [I049A-ACCT]
0
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
Depends on how you're calling the function.  If you're calling it on your form, my advice would be to populate a string, and then pass that string to the function.  Assuming that you're calling the function from your form, and that the fields on your form have the same names as the fields in the table you're querying):


dim strCriteria as string, strConcatenatedNotes as string

'populate the criteria string
strCriteria = "  [TransactionDate]= #" & me.[TransactionDate] & " AND  [NoteSequence] = '" & me.[NoteSequence] & "' and  [I049A-ACCT] = '" & me.[I049A-ACCT] & "'"

'populate the variable to hold the concatenate notes:
strConcatenatedNotes  = DConcatenate("NoteMessage", "YourTableName", strCriteria, ", ,,, True)

then set whatever destination you want the concatenated notes to populate.

If you're using the function in a query, you'll need to pass in the fields in a column in your query grid.

Hope that helps!  (and remember that since you want to order the NoteMessage values by the [TransactionSequence] value, the function needs to be modified to accept an OrderBy argument.  :)  )
0
Gustav BrockCIOCommented:
we are trying to concatenate only the [notes] that have the same [notesequence], same [transactiondate], order by [transactionsequence]

Yes, I left out the NoteSequence.
But your example shown doesn't match the file you supplied. If you don't filter on TellerId, you will have 23 records with lots of duplicate TransactionSequence as the max. value is 11.

So, given the data file, what should the output be for 2018-03-12, Teller 7676, NoteSequence 3?
Your data returns this:

TransactionTime	TransactionSequence	TellerId	NoteMessage	NoteSequence
6205	1	7676	OB-Outbound       3
6205	2	7676	Borrower Intent - 3
6205	3	7676	RFD007 -          3

Open in new window

TransactionTime matches the date but it easier to implement as a key.
0
JoeMommasMommaAnalystAuthor Commented:
Hell ,, please disregard the text file .  choosing MAX NoteSequence exlcuded records needed.  Also, I was  mistaken about how the information is organized on the server.
Can you work with the excel file I have attached? Query14.xlsx


If you take a look at the excel file , on a tab named 7676, I  have it filtered for the date of 3/12/2018 ,by  employee 7676, consistent with the scenario we have been playing with.

there are a total of 102 records for notes made on 14 accounts.
there is another tab called "one account".  

in order for the notes to appear in the right order, we have to first sort by NoteSequence largest to smallest,
then by TransactionSequence, Smallest to largest.

then concatenate the values in that order that have the same NoteSequence.
 

I should mention that this is for a query that will run every night, unattended, to update a back-end database. Every day the information will be different. What we are a capturing is the last note entered by the employee assigned to an account.
0
Gustav BrockCIOCommented:
My solution didn't filter on the NoteSequence.
To do so, a compound text key can be build like this:

Key: CStr([TransactionTime]) & CStr([TellerId]) & Left("0" & CStr([NoteSequence]);2)

Open in new window


and then a variation of the function, that takes a text key as argument, can be used:

Public Function ConcatenateRecordsTxt( _
  ByVal strSource As String, _
  ByVal strKey As String, _
  ByVal strField As String, _
  Optional ByVal strSeparator As String = ";") _
  As String

' Concatenates values from one field (strField) from all
' records in query strSource using parameter Value strKey.
' Values are separated by strSeparator.
' Default output like:
' 34;56;34;67;234
'
' 1999-10-12. Cactus Data ApS, CPH

  Dim dbs         As DAO.Database
  Dim qdf         As DAO.QueryDef
  Dim rst         As DAO.Recordset
  Dim fld         As DAO.Field
    
  Dim booPluralis As Boolean
  Dim strFields   As String
  
  On Error GoTo Err_ConcatenateRecordsTxt
  
  Set dbs = CurrentDb()
  
  If Len(strSource) > 0 And Len(strField) > 0 Then
    Set qdf = dbs.QueryDefs(strSource)
    qdf.Parameters(0) = strKey
    Set rst = qdf.OpenRecordset()
    Set fld = rst.Fields(strField)
    
    With rst
      While Not .EOF
        If booPluralis = True Then
          ' There is more than one record.
          ' Add separator.
          strFields = strFields & strSeparator
        End If
        strFields = strFields & Trim(fld.Value)
        booPluralis = True
        .MoveNext
      Wend
      .Close
    End With
    
    Set fld = Nothing
    Set rst = Nothing
    Set qdf = Nothing
  End If
  
  Set dbs = Nothing
  
  ConcatenateRecordsTxt = strFields
  
Exit_ConcatenateRecordsTxt:
  Exit Function
  
Err_ConcatenateRecordsTxt:
  MsgBox "Error " & Err.Number & ". " & Err.Description
  Resume Exit_ConcatenateRecordsTxt
  
End Function

Open in new window

The first query, NotesTimeTeller , then will read:

PARAMETERS 
    [Key] Text ( 255 );
SELECT 
    Notes.TransactionTime, 
    Notes.TransactionSequence, 
    Notes.TellerId, 
    Notes.NoteSequence, 
    Notes.NoteMessage
FROM 
    Notes
WHERE 
    (((Notes.TransactionTime)=Val(Mid([Key],1,4))) AND ((Notes.TellerId)=Val(Mid([Key],5,4))) AND ((Notes.NoteSequence)=Val(Mid([Key],9,2))))
ORDER BY 
    Notes.TransactionSequence;

Open in new window

The final query goes like this:

SELECT 
    Notes.LoanNumber, 
    Notes.TransactionDate, 
    Notes.TransactionTime, 
    Notes.TransactionCode, 
    Notes.TellerId, 
    Notes.NoteArea, 
    Notes.CreateDate, 
    Notes.ModifiedDate, 
    CStr([TransactionTime]) & CStr([TellerId]) & Left("0" & CStr([NoteSequence]),2) AS [Key], 
    ConcatenateRecordsTxt("NotesTimeTeller",CStr([TransactionTime]) & CStr([TellerId]) & Left("0" & CStr([NoteSequence]),2),"NoteMessage"," - ") AS NoteMessages
FROM 
    Notes
GROUP BY 
    Notes.LoanNumber, 
    Notes.TransactionDate, 
    Notes.TransactionTime, 
    Notes.TransactionCode, 
    Notes.TellerId, 
    Notes.NoteArea, 
    Notes.CreateDate, 
    Notes.ModifiedDate, 
    CStr([TransactionTime]) & CStr([TellerId]) & Left("0" & CStr([NoteSequence]),2);

Open in new window

0
JoeMommasMommaAnalystAuthor Commented:
i think you underestimate what I do not know. The arguments; where are they entered? within the vba?
0
Gustav BrockCIOCommented:
Copy-paste the function into a new module. Compile and save.

Then use the SQL to create the two queries. If you table isn't named Notes, adjust that in the queries.
0
JoeMommasMommaAnalystAuthor Commented:
I created the key exactly as you instructed.  Save the code compiled the database. the NotesTimeTeller query runs successfully.
 the final query does run, but does not concatenate the NoteMessage field ; it outputs blank. there is an error message that reads  "error 3265. Item not found in this collection". any thoughts?
0
Gustav BrockCIOCommented:
That means that something is misspelled.

But study the attached demo. It works.
Demo1.zip
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Solution and demo application provided
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.