Combining values from the same field in an Access query?

[sellert]

I have an application that has documents, the users can add comments for a particular document, this creates a one to many relationship. Now I am trying to run a routine that will create metrics and the users want to see all of the comments. I don't want the document to be listed more than once. This means I need to have all of the comments print together for the one time listing of the document.

In an Access query how do I combine several records for a document into one so I can use it in the report?

Thanks,
SELLERT

If I'm not learning I must be coasting. If I am coasting I must be going down hill.

 

[TheAceMan1]

How are ya sellert . . .

This is quite a different perspective from your latest post in thread705-1349969.

What you need is a function that returns the fully concatenated comments and with at least a full line space between each! Just incase users include a line space(s) in their comments the following function pre-appends each comment with [blue]*** New Comment ***[/blue]. Understand since I don't know your table structure, this is just a shot! . . .

[blue]Public Function PackComments(ID As Long) As String
   Dim db As Database, rst As DAO.Recordset, SQL As String, Build As String
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   Set db = CurrentDb
   DL = vbNewLine & vbNewLine
   SQL = "SELECT Comments " & _
         "FROM TableName " & _
         "WHERE CommentsID = " & ID & ";"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If rst.BOF Then
      Msg = "No Comments for this record!"
      Style = vbInformation + vbOKOnly
      Title = "No Comments Detected! . . ."
      MsgBox Msg, Style, Title
   Else
      Do Until rst.EOF
         If Build <> "" Then
            Build = Build & DL & rst!Comments
         Else
            Build = rst!Comments
         End If
         
         rst.MoveNext
      Loop
   End If
   
   PackComments = Build
   
   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Zion7]

Sellert, otside of Ace's code, you consider either
using "Totals/Grouping" query(toggling the Sum icon
on the menu bar, in the query pane), or just downright
use the grouping properties on the report,
from design view (preferred!).

 

[PHV]

Have a look here:
faq701-4233

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sellert]

I need to do this outside of code within the access query design view. Also, I know oder than dirt - I am using access 97. To be converted soon though.

Thanks,
SELLERT

If I'm not learning I must be coasting. If I am coasting I must be going down hill.

 

[PHV]

I need to do this outside of code
So, why asking in the Access Modules (VBA Coding) Forum ?

 

[randy700]

Create your query with both fields as output.
Build a new form or report based on the query.
Show the document name once, such as in the form header.
Display all comments, such as in a continuous form.


Randy