none
Creating string data based on a summary control field.

    Question

  • Have a problem - need your help.

    Need to create a string of data based on a key field.  See ex. below.

    Input:

    MS-Access Table - activity by date.  Ex...

    Key          Data

    0001         08/01/2010-K1-Status report 1

    0001         08/07/2010-k1-Status report 2

    0001         ...

    0002         08/03/2010-k2-Status report 1

    0002         ...

    0003         08/04/2010-k3-status report 1

    0003         08/05/2010-k3-status report 2

    0003         ...

     

    Desired Output:

    Key         Data

    0001       08/01/2010-K1-Status report 1   08/07/2010-k1-Status report 2   ...

    0002       08/03/2010-k2-Status report 1    ... 

    0003       08/04/2010-k3-status report 1   08/05/2010-k3-status report 2   ...

    How can I do it in MS-Access?

    Thanking you in advance.

    JEB-MHS

     

     

    Wednesday, September 08, 2010 1:36 PM

Answers

  • Here's another solution using an Access report.

     

    This solution uses Grouping and Sorting and it assumes you don't mind sorting by the Key and Activity fields.

     

    Here's a step-by-step procedure (to explain the details).

     

     

    1.    Create a new report based on the table.

     

    2.    In report design view, click the Sorting and Grouping button to open the Sorting and Grouping dialog.  In the dialog:

     

    (a)    In the Field/Expression column, click in the first row, click the down-arrow that appears on the right, and select the Key field.  

    (b)    With the cursor still in the first row, click in the Group Header and Group Footer properties and select Yes. 

    (c)    In the Field/Expression column, click in the second row, click the down-arrow that appears on the right, and select the Activity field.  (You don't need Group Headers/Footers for this field.)

    (d)    Close the Sorting and Grouping dialog.

     

    3.    Double-click the Group Header bar to open the properties sheet for the Group Header.  In the properties sheet, perform these steps:

     

    (a)    Set the Visible property to No.
    (You don't want to print the Group Header. You only want to use the Group Header's Format event, explained next.)

    (b)    Click in the On Format property, click the down-arrow that appears on the right, select [Event Procedure] and click the build button (...) on the right.  The code window opens. 

    (c)    Make the code window look like this:

     

    Option Compare Database

    Option Explicit

     

    Private mvarGroupFooterText As Variant

     

     

    Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)

     

        '   Reset variable when new key begins:

        mvarGroupFooterText = Null

     

    End Sub

     

     

    4.    Press ALT-F11 (or the report on the Taskbar) to return to report-design view.

     

    5.    Click the Field List button to open the field list.  Drag the Activity field from the Field List to the Detail Section.  Double-click the resulting TextBox to open its properties sheet.  In the properties sheet, perform these steps:

     

    (a)    Change the Name property to txtActivity. 

    (b)    Close the properties sheet. 

     

    6.    Double-click the Detail bar to open the properties sheet for the Detail Section.  In the properties sheet, perform these steps:

     

    (a)    Set the Visible property to No.
    (You don't want the Detail Section to print. You only want to use the Detail Section's Format event, explained next.)

    (b)    Click in the On Format property, click the down-arrow that appears on the right, select [Event Procedure] and click the build button (...) on the right. 

    (c)    Add the following to the code window:

     

     

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

     

        '   Concatenate the text for each record.

        '       Use the plus (+) sign to concatenate the ", ".

        '       This is Null propagation, which makes the ", "

        '       vanish when Me.txtActivity is Null.

        mvarGroupFooterText = mvarGroupFooterText & (Trim(Me.txtActivity) + ", ")

     

    End Sub

     

     

    7.    Press ALT-F11 (or the report on the Taskbar) to return to report-design view.

     

    8.    If the field list isn't open, click the Field List button to open the field list.  Drag the Key field from the field list to the left side of the Group Footer.  Close the field list.

     

    9.    Create an unbound TextBox on the right side of the Group Footer.  Double-click the TextBox to open its properties sheet.  In the properties sheet, perform these steps:

     

    (a)    Change the Name property to txtGroupFooter.

    (b)    Change the Can Grow property to Yes.

    (c)    Close the properties sheet.

     

    10.  Double-click the Group Footer bar to open the properties sheet for the Group Footer.  In the properties sheet, perform these steps:

     

    (a)    Click in the On Format property, click the down-arrow that appears on the right, select [Event Procedure] and click the build button (...) on the right.

    (b)    Add the following to the code window:

     

     

    Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)

     

        '   Remove final ", ":

        If Not IsNull(mvarGroupFooterText) Then

            mvarGroupFooterText = Left(mvarGroupFooterText, _

                Len(mvarGroupFooterText) - 2)

        End If

     

        '   Put the string in the footer:

        Me.txtGroupFooter = mvarGroupFooterText

     

    End Sub

     

     

    11.  The complete code in the code window should now look like this:

     

     

    Option Compare Database

    Option Explicit

     

    Private mvarGroupFooterText As Variant

     

     

    Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)

     

        '   Reset variable when new key begins:

        mvarGroupFooterText = Null

     

    End Sub

     

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

     

        '   Concatenate the text for each record.

        '       Use the plus (+) sign to concatenate the ", ".

        '       This is Null propagation, which makes the ", "

        '       vanish when Me.txtActivity is Null.

        mvarGroupFooterText = mvarGroupFooterText & (Trim(Me.txtActivity) + ", ")

     

    End Sub

     

    Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)

     

        '   Remove final ", ":

        If Not IsNull(mvarGroupFooterText) Then

            mvarGroupFooterText = Left(mvarGroupFooterText, _

                Len(mvarGroupFooterText) - 2)

        End If

     

        '   Put the string in the footer:

        Me.txtGroupFooter = mvarGroupFooterText

     

    End Sub

     

     

    12.  Press ALT-F11 (or the report on the Taskbar) to return to report-design view.

     

    13.  Save the report.

     

    14.  Run the report.

     

     

    Edited to take account of the possibility that a record may contain Null in the Activity field.

     

    Sunday, September 12, 2010 1:31 AM

All replies

  • In Access, one way to accomplish this is with a custom function in a GROUP BY query.  You can use the custom function to aggregate (concatenate in this example) the Data field values for each key.  In SQL Server, you can create what is called a custom aggregate.  In Access, we can just use a custom function to do the aggregation for us.

    The custom function might look like this:

    Public Function Concat(sKey As String) As String
        Dim rs As DAO.Recordset
        Dim sData As String
       
        Set rs = CurrentDb.OpenRecordset("SELECT [Data] FROM MyTable WHeRE Key='" & sKey & "'")
       
        Do Until rs.EOF
            sData = sData & " " & rs("Data")
            rs.MoveNext
        Loop
       
        Concat = Trim(sData)
       
    End Function

    For the query, the SQL might look like the following:

    SELECT Table2.Key, Concat([Key]) AS Concatenation
    FROM MyTable
    GROUP BY Table2.Key;

    HTH,

    David Lloyd
    Lemington Consulting
    http://Lemingtonit.com

     

    Friday, September 10, 2010 1:56 AM
  • Here's another solution using an Access report.

     

    This solution uses Grouping and Sorting and it assumes you don't mind sorting by the Key and Activity fields.

     

    Here's a step-by-step procedure (to explain the details).

     

     

    1.    Create a new report based on the table.

     

    2.    In report design view, click the Sorting and Grouping button to open the Sorting and Grouping dialog.  In the dialog:

     

    (a)    In the Field/Expression column, click in the first row, click the down-arrow that appears on the right, and select the Key field.  

    (b)    With the cursor still in the first row, click in the Group Header and Group Footer properties and select Yes. 

    (c)    In the Field/Expression column, click in the second row, click the down-arrow that appears on the right, and select the Activity field.  (You don't need Group Headers/Footers for this field.)

    (d)    Close the Sorting and Grouping dialog.

     

    3.    Double-click the Group Header bar to open the properties sheet for the Group Header.  In the properties sheet, perform these steps:

     

    (a)    Set the Visible property to No.
    (You don't want to print the Group Header. You only want to use the Group Header's Format event, explained next.)

    (b)    Click in the On Format property, click the down-arrow that appears on the right, select [Event Procedure] and click the build button (...) on the right.  The code window opens. 

    (c)    Make the code window look like this:

     

    Option Compare Database

    Option Explicit

     

    Private mvarGroupFooterText As Variant

     

     

    Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)

     

        '   Reset variable when new key begins:

        mvarGroupFooterText = Null

     

    End Sub

     

     

    4.    Press ALT-F11 (or the report on the Taskbar) to return to report-design view.

     

    5.    Click the Field List button to open the field list.  Drag the Activity field from the Field List to the Detail Section.  Double-click the resulting TextBox to open its properties sheet.  In the properties sheet, perform these steps:

     

    (a)    Change the Name property to txtActivity. 

    (b)    Close the properties sheet. 

     

    6.    Double-click the Detail bar to open the properties sheet for the Detail Section.  In the properties sheet, perform these steps:

     

    (a)    Set the Visible property to No.
    (You don't want the Detail Section to print. You only want to use the Detail Section's Format event, explained next.)

    (b)    Click in the On Format property, click the down-arrow that appears on the right, select [Event Procedure] and click the build button (...) on the right. 

    (c)    Add the following to the code window:

     

     

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

     

        '   Concatenate the text for each record.

        '       Use the plus (+) sign to concatenate the ", ".

        '       This is Null propagation, which makes the ", "

        '       vanish when Me.txtActivity is Null.

        mvarGroupFooterText = mvarGroupFooterText & (Trim(Me.txtActivity) + ", ")

     

    End Sub

     

     

    7.    Press ALT-F11 (or the report on the Taskbar) to return to report-design view.

     

    8.    If the field list isn't open, click the Field List button to open the field list.  Drag the Key field from the field list to the left side of the Group Footer.  Close the field list.

     

    9.    Create an unbound TextBox on the right side of the Group Footer.  Double-click the TextBox to open its properties sheet.  In the properties sheet, perform these steps:

     

    (a)    Change the Name property to txtGroupFooter.

    (b)    Change the Can Grow property to Yes.

    (c)    Close the properties sheet.

     

    10.  Double-click the Group Footer bar to open the properties sheet for the Group Footer.  In the properties sheet, perform these steps:

     

    (a)    Click in the On Format property, click the down-arrow that appears on the right, select [Event Procedure] and click the build button (...) on the right.

    (b)    Add the following to the code window:

     

     

    Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)

     

        '   Remove final ", ":

        If Not IsNull(mvarGroupFooterText) Then

            mvarGroupFooterText = Left(mvarGroupFooterText, _

                Len(mvarGroupFooterText) - 2)

        End If

     

        '   Put the string in the footer:

        Me.txtGroupFooter = mvarGroupFooterText

     

    End Sub

     

     

    11.  The complete code in the code window should now look like this:

     

     

    Option Compare Database

    Option Explicit

     

    Private mvarGroupFooterText As Variant

     

     

    Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)

     

        '   Reset variable when new key begins:

        mvarGroupFooterText = Null

     

    End Sub

     

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

     

        '   Concatenate the text for each record.

        '       Use the plus (+) sign to concatenate the ", ".

        '       This is Null propagation, which makes the ", "

        '       vanish when Me.txtActivity is Null.

        mvarGroupFooterText = mvarGroupFooterText & (Trim(Me.txtActivity) + ", ")

     

    End Sub

     

    Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)

     

        '   Remove final ", ":

        If Not IsNull(mvarGroupFooterText) Then

            mvarGroupFooterText = Left(mvarGroupFooterText, _

                Len(mvarGroupFooterText) - 2)

        End If

     

        '   Put the string in the footer:

        Me.txtGroupFooter = mvarGroupFooterText

     

    End Sub

     

     

    12.  Press ALT-F11 (or the report on the Taskbar) to return to report-design view.

     

    13.  Save the report.

     

    14.  Run the report.

     

     

    Edited to take account of the possibility that a record may contain Null in the Activity field.

     

    Sunday, September 12, 2010 1:31 AM
  • Of course, you could just create a query as I mentioned and use it as the basis of a report.  It should also be pointed out that this methodology is not new as it is the same as stated in the following KB article: http://support.microsoft.com/kb/210163.

    David Lloyd
    Lemington Consulting

    Thursday, September 16, 2010 1:16 AM
  • Thank you.  I'll give it a shot once I absorb what you are proposing :).  JEB-MHS
    JEB-MHS
    Wednesday, September 22, 2010 7:57 PM
  • Thank you.  JEB-MHS


    JEB-MHS
    Wednesday, September 22, 2010 7:58 PM
  • Thanks.
    JEB-MHS
    Wednesday, September 22, 2010 7:59 PM