Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2011
    Posts
    42

    Unanswered: gathering data from two records into one field

    i have one table , having two columns : employee name and mark , if i have two records for one employee and two marks (5) in the first record and (7) in the second record; is there any code to gather both of them to be (5,7)
    thanks

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Two possible solutions:
    Code:
    Function ConcatGroupRecords(ByVal TableName As String, ByVal GroupColumnName As String, ByVal ConcatColumnName As String) As Variant
    '
    ' Input
    ' -----
    ' TableName:        Name of the source table or query.
    ' GroupColumnName:  Name of the column to group by.
    ' ConcatColumnName: Name of the column containing the values to concatenate
    '
    ' Returns
    ' -------
    ' A 2 dimensions array:
    '
    '    | 0          | 1
    '----+------------+--------------
    ' 0  | Group By   | Concatenated
    ' to | Values     | Values
    ' n  |
    '
        Dim dbs As DAO.Database
        Dim rstG As DAO.Recordset
        Dim rstL As DAO.Recordset
        Dim strRetVal As String
        Dim strSQL As String
        Dim varArray() As Variant
        
        strSQL = "SELECT " & GroupColumnName & " FROM " & TableName & " GROUP BY " & GroupColumnName & ";"
        Set dbs = CurrentDb
        Set rstG = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
        With rstG
            Do Until .EOF
                strSQL = "SELECT " & ConcatColumnName & " FROM " & TableName & " WHERE " & GroupColumnName & " = '" & .Fields(GroupColumnName).Value & "';"
                Set rstL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
                With rstL
                    strRetVal = ""
                    Do Until .EOF
                        If Len(strRetVal) Then strRetVal = strRetVal & ","
                        strRetVal = strRetVal & Nz(.Fields(ConcatColumnName).Value, "")
                        .MoveNext
                    Loop
                    .Close
                    
                End With
                ReDim Preserve varArray(0 To 1, 0 To .AbsolutePosition)
                varArray(0, .AbsolutePosition) = .Fields(GroupColumnName).Value
                varArray(1, .AbsolutePosition) = strRetVal
                .MoveNext
            Loop
            .Close
        End With
        Set rstG = Nothing
        Set rstL = Nothing
        Set dbs = Nothing
        ConcatGroupRecords = varArray
        
    End Function
    Or:
    Code:
    Function ConcatRows(ByVal TableName As String, ByVal GroupColumnName As String, ByVal ConcatColumnName As String, ByVal GroupValue As String) As String
    '
    ' Input
    ' -----
    ' TableName:        Name of the source table or query.
    ' GroupColumnName:  Name of the column to group by.
    ' ConcatColumnName: Name of the column containing the values to concatenate.
    ' GroupValue:       Value in the GroupColumnName to search for.
    '
    ' Returns:          String containing the concatenated values.
    ' -------
    '
    ' Use in a Query:
    ' --------------
    ' SELECT <TableName>.<GroupColumnName>, ConcatRows('<TableName>','<GroupColumnName>','<ConcatColumnName>',[<GroupColumnName>]) AS CValues
    ' FROM <TableName>
    ' GROUP BY <TableName>.<GroupColumnName>;
    '
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strRetVal As String
        Dim strSQL As String
        
        Set dbs = CurrentDb
        strSQL = "SELECT " & ConcatColumnName & " FROM " & TableName & " WHERE " & GroupColumnName & " = '" & GroupValue & "';"
        Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                If Len(strRetVal) Then strRetVal = strRetVal & ","
                strRetVal = strRetVal & Nz(.Fields(ConcatColumnName).Value, "")
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        Set dbs = Nothing
        ConcatRows = strRetVal
        
    End Function
    Have a nice day!

  4. #4
    Join Date
    Mar 2011
    Posts
    42
    THANKS, but unfortunately not succedded, can u pls, pls, provide a sample ?

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like this:

    Table: Table4
    -------------
    Code:
    SysCounter:     Name:	Value:
    1	           rf	1
    2	           rf	3
    3	           rf	5
    4	           rf	2
    5	           rd	4
    6	           rd	2
    7	           rf	8
    8	           kl	3
    Query: Qry_ConcatRows
    -----------------------
    Code:
    SELECT Table4.Name, 
    ConcatRows('Table4','Name','Value',[Name]) AS CValues
    FROM Table4
    GROUP BY Table4.Name;
    Result:
    ------
    Code:
    Name	CValues
    kl	3
    rd	4,2
    rf	1,3,5,2,8
    OR:
    Code:
    Function Test_ConcatGroupRecords()
    
        Dim strTableName As String
        Dim strGroupColumnName As String
        Dim strConcatColumnName As String
        Dim varArray As Variant
        Dim i As Integer
        
        strTableName = "Table4"
        strGroupColumnName = "Name"
        strConcatColumnName = "Value"
        varArray = ConcatGroupRecords(strTableName, strGroupColumnName, strConcatColumnName)
        Debug.Print strGroupColumnName, strConcatColumnName
        For i = 0 To UBound(varArray, 2)
            Debug.Print varArray(0, i), varArray(1, i)
        Next i
          
    End Function
    Debug Window:
    --------------
    Code:
    Name          Value
    kl            3
    rd            4,2
    rf            1,3,5,2,8
    Have a nice day!

  6. #6
    Join Date
    Mar 2011
    Posts
    42
    Many thanks for your response,
    something is going wrong with me, when I tried within the code it's giving response that (expected =)

    I'm in a bad need to resolve this ----- help me please

    thanks again

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Hard to figure out what's going wrong with you if you don't post your code. Or your db with the failing effort.
    Paul

  8. #8
    Join Date
    Mar 2011
    Posts
    42
    Quote Originally Posted by pbaldy View Post
    Hard to figure out what's going wrong with you if you don't post your code. Or your db with the failing effort.
    Dear PDaldy,

    I'm so grateful, finally it's ok. u r great

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •