Convert grouped records into one string

[Mmattson]

I am trying to convert all the names I have listed in a grouped query into one string record. For example, I am 1) grouping my records by date, 2) each date has a number of records for who was absent that day. I want to convert those names of absentees into a string:

4/1/04 Bob
4/1/04 Bill
4/1/04 Betty
4/3/04 Dave
4/3/04 Dan

...would become...

4/1/04 Bob, Bill, Betty
4/3/04 Dave, Dan

Any help would be....well...helpful.

 

[raskew]

This is a classic looping situation.

Copy / paste the following code in a new module.
Change table & field names to agree with yours,
then let it rip.

Public Sub StrAbsences()
'*******************************************
'Purpose:   see:
'           [url]http://www.access-programmers.co.uk/forums/showthread.php?t=67164[/url]
'Author:    raskew
'Inputs:    from debug window:
'           call strAbsences <enter>
'Output:    4/1/04: Dewey, Louie, Huey
'           4/3/04: Scrooge, Daffy
'*******************************************

Dim db      As DATABASE
Dim rs      As Recordset
Dim strHold As String
Dim strSQL  As String
Dim n       As Integer
Dim dteHold As Date

    Set db = CurrentDb
    
    strSQL = "SELECT tblAbsences.absDate, tblAbsences.absName" _
          & " FROM tblAbsences" _
          & " ORDER BY tblAbsences.absDate;"
    
    Set rs = db.OpenRecordset(strSQL)
    
    Do While Not rs.EOF
        dteHold = rs!absDate
        strHold = ""
        Do While rs!absDate = dteHold
           strHold = strHold & rs!absName & ", "
           rs.MoveNext
           If rs.EOF Then Exit Do
        Loop
        n = Len(Trim(strHold))
        'get rid of the trailing ","
        strHold = dteHold & ": " & Left(strHold, n - 1)
        Debug.Print strHold
    Loop
    
    'recover some memory
    rs.Close
    db.Close
    Set db = Nothing
       
End Sub

HTH - Bob

 

[Mmattson]

OK...Thanks...the Immediate Window shows me the output I want.

The question now is, how do I activate this for a report or to have it populate a field in a table or query?

I am familiar with activating modules with a control in a form, but I don't know how to get the output from this module into a control on a report or to a field in a query or table.

Please help one last time....

 

[Jon K]

To do it in a query, you can create a public function in a module.

See the query and the function in the attached database.

 

[Mmattson]

Are there any changes to this when using an autonumbered field (as opposed to date)? When I use my primary key [MeetingID] instead of [Date] I get "No value given for one or more required parameters"

I am using Jon's solution.

Thanks,
Marcus

 

[Jon K]

If MeetingID is a numeric field, you can change the SQL string from:-
SQL = "SELECT [Name] FROM [tblData] WHERE [Date]=#" & ID & "#"

to:-
SQL = "SELECT [Name] FROM [tblData] WHERE [MeetingID]=" & ID
.

 

[alktrigger]

I am resurrecting this baby!

I have modified the code in the attachment to fit my need, it is as follows:

Public Function Conc(ID) As String
   Dim cnn As ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim SQL As String
   Dim sConc As String
   'Dim rs As Recordset
   'Dim cnn As String
   
   Set cnn = CurrentProject.Connection
   
'  SQL = "SELECT [Name] FROM [tblData] WHERE [Date]=#" & ID & "#"
 ' SQL = "SELECT [Name] FROM [tblData] WHERE [MeetingID]=" & ID
   SQL = "SELECT [Issue] FROM [tblReportDump] WHERE [ShipNum]=" & ID
   
   rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
   Do While Not rs.EOF
     sConc = sConc & ", " & rs![Issue]
     If sConc = "," Then
        sConc = ""
     End If
     rs.MoveNext
   Loop
   sConc = Mid(sConc, 3)    'Remove leading comma and space.
   
   Set rs = Nothing
   Set cnn = Nothing
   
   Conc = sConc
   
End Function

This works well when the data is coming off of a table, What can i do to make pull data from a query?