Using a query to concatenate data

[ocampod]

Hi guys,



Let me provide you with some background information on my situation. I have a table that acts as an audit trail. Whenever a change is made in one particular table, a macro runs and appends the changes to my audit trail table.

The audit trail table contains a description of the change, when it was modified and who modified it.

In a simple select query, I am able to concatenate the description (a memo field), the date modified (date field) and the person (a text field)who made the modifications into one column (with some HTML). However, I can't figure out how to concatenate rows that are related to a specific record.

For example, let's say that I created record 1 yesterday. The macro will append that information to the audit trail. Now, let's say that I make a change on record one. Again, the macro captures this information.

Now, the audit trail shows two entries for record 1, each with their distinct description and time stamp. I simply need a query that can take these two records and merge them into one.

Please see my basic example:

Record 1 "Created yesterday" 01/06/2011 David; "Updated today" 01/07/2011 David

There's some VBA code out there, but I think it failed on me because I'm trying to concatenate memo fields, but who knows.

[pbaldy]

What you're describing is this type of thing:

Return a concatenated list of sub-record values

Though I can't promise it will work with memo fields. I rarely use them.

[ocampod]

Hi Paul,

I tried a variant of this code and I was receiving some errors, possibly related to the Memo field. I used the Immediate Window to track my results, but garbage data was displayed. I'll give it another shot.

If possible, would it be OK to post my code just to review what I may be doing wrong?

[pbaldy]

Of course.

[ocampod]

Ok, the code I am using comes from here:

http://www.mvps.org/access/modules/mdl0008.htm

I tweaked it to fit my needs. For example, the sample references a table, but I am referencing a query, called "Version Data". The query partially concatenated my results, as was previously mentioned above.

The query has two fields: Project Log ID and History. Project Log ID is the unique id and History displays the combined data from description, date modified and modified by. Please note that History contains some Rich text, so I don't know if they may be a problem.

My code is practically the same, except I removed the WHERE clause (b/c I need to concatenate all records, not just a few) and I commented out the Select Case block. Everything runs fine, until the actual query runs in which I get an error.

Here's the code:

'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Public Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String) _
As String
'Returns mutiple field values for each unique value
'of another field in a single table
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatFld(("Customers","ContactTitle","CustomerI D", _
' "string","Owner")
'Where Customers = The parent Table
' ContactTitle = The field whose values to use for lookups
' CustomerID = Field name to concatenate
' string = DataType of ContactTitle field
' Owner = Value on which to return concatenated CustomerID
'
Dim lodb As Database, lors As Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Const cQ = """"

On Error GoTo Err_fConcatFld

lovConcat = Null
Set lodb = CurrentDb

loSQL = "SELECT [" & stFldToConcat & "] FROM [" & stTable & "]"

' Select Case stForFldType
' Case "String":
' loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ
' Case "Long", "Integer", "Double": 'AutoNumber is Type Long
' loSQL = loSQL & "[" & stForFld & "] = " & vForFldVal
' Case Else
' GoTo Err_fConcatFld
' End Select

Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)

'Are we sure that duplicates exist in stFldToConcat
With lors
If .RecordCount <> 0 Then
'start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & "; "
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatFld = lovConcat


Exit_fConcatFld:
Set lors = Nothing: Set lodb = Nothing
Exit Function

Err_fConcatFld:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
Resume Exit_fConcatFld
End Function
'************ Code End **********

[ocampod]

Now that I think about it, the code mentioned above may not be what's appropriate for this situation.

[pbaldy]

I think the one I posted is more appropriate, though they are similar. Can you post a sample of your db? I think you want the WHERE clause, so you only get the values applicable to a given record ("Record1" in your example".

[ocampod]

Paul,

Attached is a sample of my database. The tables are linked to SharePoint lists, which contain HTML tags. The table I am trying to query is called "Versions" and the query associated to it is called "Version Data". "Version Data" kind of concatenated the data.

My code is in the module called "modGetVersions"

Let me know if you need anything else and I appreciate your help.

David

[pbaldy]

Probably the Friday afternoon blah's, but I'm confused. In the Versions table, I expected to see repetition in the Project Log ID field, and that you were trying to concatenate multiple records together that pertained to the same ID. In other words, to use your earlier example, the data would look like:

Record 1 "Created yesterday" 01/06/2011 David
Record 1 "Updated today" 01/07/2011 David

but you want to see

Record 1 "Created yesterday" 01/06/2011 David; "Updated today" 01/07/2011 David

Can you clarify what you want this query to do?

[ocampod]

Paul,

I apologize for the confusion. Most of the records in that table are new and haven't had updates. There are a few, however, that have had some sort of update. For example, filter for Project Log ID # 52. An update was found because the append query found a currently existing Project Log ID (52) but a discrepancy in the modified date.

Thus, tomorrow, there may be another update to that particular log item (52) and an entry would be added to the table. So, we would then see 3 records related to that project log, but I would like the query to concatenate all related items into one entry.

So, if we used project log item 52 as an example, I would like it to say:

Project ID History
52 Created 1/4/2011 John Doe <br /> Updated once 1/5/2011 Jane Doe <br /> 3rd record 1/7/2011 Webster

I hope this makes sense.

[ocampod]

Paul,

Would you happen to know any other solutions?

Thanks,

David

[pbaldy]

Sorry, I was out of town for the weekend. So in your example, you don't actually the memo field anyway? Are you determining "Created" by the first record, since it's not in the data anywhere? And then any other would say "Modified"?

[ocampod]

Paul,

It seems that we are confusing each other and I apologize for that. I uploaded two pics that I hope will clarify a few things.

The first table, the "Versions Table" picture depicts how the versions table laid out. I am currently capturing all new entries (denoted by a unique project ID) and updates to entries (denoted by duplicate project ID entries along with a time stamp).

So, when a record is created for the first time, only one unique ID will be there. Now, if an update to a record is made, then we'll see duplicate entries associated to the same project ID.

So, to answer your question, yes. The "Created" is determined by the first record. All other entries associated to the same project ID are considered "Modified Date" entries.

Now, the query should take the contents of the memo field, the modified date and modified by fields and 'concatenate' them into a single field (description). I believe I entered some HTML tags to simulate line breaks (please see the Versions Query picture as a reference). The versions query is also how I would like the data to appear.

Please let me know if you need me to further explain.

Thanks!

[pbaldy]

I think I understand, but your examples and pictures bear little resemblance to the sample db you posted, which confuses things. I can't achieve the result pictured because that data isn't there. I'll try and throw something together.

[pbaldy]

What field/table does project log ID relate to? I assumed ID in Project Log, but they're different data types, which doesn't make sense. See if this does what you want:

Public Function fConcatFld(lngProjID As Long) As String

'
  Dim lodb As dao.Database, lors As dao.Recordset
  Dim lovConcat As Variant, loCriteria As String
  Dim loSQL As String
  Dim booFirst As Boolean
  Const cQ = """"

  On Error GoTo Err_fConcatFld

  lovConcat = Null

  Set lodb = CurrentDb

  loSQL = "SELECT Modified, Description, [Modified By] FROM Versions WHERE [Project Log ID] = '" & lngProjID & "'"


  Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)
  booFirst = True
  'Are we sure that duplicates exist in stFldToConcat
  With lors
    If .RecordCount <> 0 Then
      'start concatenating records
      Do While Not .EOF
        If booFirst = True Then
          lovConcat = lovConcat & "Created By "
          booFirst = False
        Else
          lovConcat = lovConcat & "Modified By "
        End If

        lovConcat = lovConcat & lors![Modified by] & lors("Modified") & lors!Description & ";<BR/> "
        
        .MoveNext
      Loop
    Else
      GoTo Exit_fConcatFld
    End If
  End With

  fConcatFld = lovConcat

Exit_fConcatFld:
  Set lors = Nothing
  Set lodb = Nothing
  Exit Function

Err_fConcatFld:
  MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
  Resume Exit_fConcatFld
End Function

And a test query

SELECT [Project Log].[Project ID], fConcatFld([id]) AS ConcatenatedData, [Project Log].ID
FROM [Project Log]