none
how to make query mutli row RRS feed

  • Question

  • hi guys..

    first i have just 3 materiales

    i make example photo in photoshop to understand what i need

    i need make look like this image in access how can i make this???

    download datebase:

    https://ufile.io/tp8lb


    • Edited by Salar Music Friday, December 1, 2017 5:22 AM
    Friday, December 1, 2017 5:20 AM

Answers

  • Hello,

    No built-in function in Access could merge rows, so you could create custom function.

    SELECT Absences.Aname,ConcatRelated("Amaterial", "(SELECT Absences.Aname, Absences.Amaterial FROM Absences GROUP BY Absences.Aname, Absences.Amaterial, Absences.Astatus HAVING (((Absences.Astatus)=""absent"")))", "[Aname] = """ & [Aname]   & """" ) as [Name Material]
    FROM Absences
    GROUP BY Absences.Aname, Absences.Astatus
    HAVING (((Absences.Astatus)="absent"));
    Option Compare Database
    
    Public Function ConcatRelated(strField As String, _
        strTable As String, _
        Optional strWhere As String, _
        Optional strOrderBy As String, _
        Optional strSeparator = ", ") As Variant
    On Error GoTo Err_Handler
        'Purpose:   Generate a concatenated string of related records.
        'Return:    String variant, or Null if no matches.
        'Arguments: strField = name of field to get results from and concatenate.
        '           strTable = name of a table or query.
        '           strWhere = WHERE clause to choose the right values.
        '           strOrderBy = ORDER BY clause, for sorting the values.
        '           strSeparator = characters to use between the concatenated values.
        'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
        '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
        '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
        '           4. Returning more than 255 characters to a recordset triggers this Access bug:
        '               http://allenbrowne.com/bug-16.html
        Dim rs As DAO.Recordset         'Related records
        Dim rsMV As DAO.Recordset       'Multi-valued field recordset
        Dim strSql As String            'SQL statement
        Dim strOut As String            'Output string to concatenate to.
        Dim lngLen As Long              'Length of string.
        Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
        
        'Initialize to Null
        ConcatRelated = Null
        
        'Build SQL string, and get the records.
        strSql = "SELECT " & strField & " FROM " & strTable
        If strWhere <> vbNullString Then
            strSql = strSql & " WHERE " & strWhere
        End If
        If strOrderBy <> vbNullString Then
            strSql = strSql & " ORDER BY " & strOrderBy
        End If
        Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
        'Determine if the requested field is multi-valued (Type is above 100.)
        bIsMultiValue = (rs(0).Type > 100)
        
        'Loop through the matching records
        Do While Not rs.EOF
            If bIsMultiValue Then
                'For multi-valued field, loop through the values
                Set rsMV = rs(0).Value
                Do While Not rsMV.EOF
                    If Not IsNull(rsMV(0)) Then
                        strOut = strOut & rsMV(0) & strSeparator
                    End If
                    rsMV.MoveNext
                Loop
                Set rsMV = Nothing
            ElseIf Not IsNull(rs(0)) Then
                strOut = strOut & rs(0) & strSeparator
                If strOut = getMaterials() Then
                strOut = "Absence in all materials"
                End If
            End If
            rs.MoveNext
        Loop
        rs.Close
        
        'Return the string without the trailing separator.
        lngLen = Len(strOut) - Len(strSeparator)
        If lngLen > 0 Then
            ConcatRelated = Left(strOut, lngLen)
        End If
    
    Exit_Handler:
        'Clean up
        Set rsMV = Nothing
        Set rs = Nothing
        Exit Function
    
    Err_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
        Resume Exit_Handler
    End Function
    
    Function getMaterials() As String
    Dim rs As DAO.Recordset
    Dim strOut As String
    Set rs = DBEngine(0)(0).OpenRecordset("Select * from materials", dbOpenDynaset)
    Do While Not rs.EOF
    strOut = strOut & rs(0) & ", "
    rs.MoveNext
    Loop
    rs.Close
    getMaterials = strOut
    End Function
    

    Reference: 

    http://allenbrowne.com/func-concat.html

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.  

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Salar Music Monday, December 4, 2017 7:12 PM
    Monday, December 4, 2017 6:05 AM
    Moderator

All replies

  • Hi KORG.Pa3x,

    Do you have any issue with your original thread below?

    https://social.msdn.microsoft.com/Forums/Lync/en-US/537a4df9-1047-4116-abac-11a53b2da57a/how-to-using-count-in-query?forum=accessdev

    If not, I would suggest you mark the helpful reply as answer which is the way to close a thread here.

    If there is, please feel free to keep following.

    Regards,

    Tony


    Help each other

    Friday, December 1, 2017 7:40 AM
  • Hi KORG.Pa3x,

    Do you have any issue with your original thread below?

    https://social.msdn.microsoft.com/Forums/Lync/en-US/537a4df9-1047-4116-abac-11a53b2da57a/how-to-using-count-in-query?forum=accessdev

    If not, I would suggest you mark the helpful reply as answer which is the way to close a thread here.

    If there is, please feel free to keep following.

    Regards,

    Tony


    Help each other

    first good morning bro

    i choosed best answear in old topic

    and this current topic is a different request

    please re watch my image in this topic to understand my request

    thank you very much bro

    Friday, December 1, 2017 7:45 AM
  • any help please
    Friday, December 1, 2017 2:26 PM
  • You can use a Crosstab query fed by another query that numbers the 'Name Material.'  

    That query cannot use a subquery as a Crosstab query cannot handle a subquery ANYWHERE!


    Build a little, test a little

    Friday, December 1, 2017 6:31 PM
  • Hello,

    No built-in function in Access could merge rows, so you could create custom function.

    SELECT Absences.Aname,ConcatRelated("Amaterial", "(SELECT Absences.Aname, Absences.Amaterial FROM Absences GROUP BY Absences.Aname, Absences.Amaterial, Absences.Astatus HAVING (((Absences.Astatus)=""absent"")))", "[Aname] = """ & [Aname]   & """" ) as [Name Material]
    FROM Absences
    GROUP BY Absences.Aname, Absences.Astatus
    HAVING (((Absences.Astatus)="absent"));
    Option Compare Database
    
    Public Function ConcatRelated(strField As String, _
        strTable As String, _
        Optional strWhere As String, _
        Optional strOrderBy As String, _
        Optional strSeparator = ", ") As Variant
    On Error GoTo Err_Handler
        'Purpose:   Generate a concatenated string of related records.
        'Return:    String variant, or Null if no matches.
        'Arguments: strField = name of field to get results from and concatenate.
        '           strTable = name of a table or query.
        '           strWhere = WHERE clause to choose the right values.
        '           strOrderBy = ORDER BY clause, for sorting the values.
        '           strSeparator = characters to use between the concatenated values.
        'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
        '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
        '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
        '           4. Returning more than 255 characters to a recordset triggers this Access bug:
        '               http://allenbrowne.com/bug-16.html
        Dim rs As DAO.Recordset         'Related records
        Dim rsMV As DAO.Recordset       'Multi-valued field recordset
        Dim strSql As String            'SQL statement
        Dim strOut As String            'Output string to concatenate to.
        Dim lngLen As Long              'Length of string.
        Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
        
        'Initialize to Null
        ConcatRelated = Null
        
        'Build SQL string, and get the records.
        strSql = "SELECT " & strField & " FROM " & strTable
        If strWhere <> vbNullString Then
            strSql = strSql & " WHERE " & strWhere
        End If
        If strOrderBy <> vbNullString Then
            strSql = strSql & " ORDER BY " & strOrderBy
        End If
        Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
        'Determine if the requested field is multi-valued (Type is above 100.)
        bIsMultiValue = (rs(0).Type > 100)
        
        'Loop through the matching records
        Do While Not rs.EOF
            If bIsMultiValue Then
                'For multi-valued field, loop through the values
                Set rsMV = rs(0).Value
                Do While Not rsMV.EOF
                    If Not IsNull(rsMV(0)) Then
                        strOut = strOut & rsMV(0) & strSeparator
                    End If
                    rsMV.MoveNext
                Loop
                Set rsMV = Nothing
            ElseIf Not IsNull(rs(0)) Then
                strOut = strOut & rs(0) & strSeparator
                If strOut = getMaterials() Then
                strOut = "Absence in all materials"
                End If
            End If
            rs.MoveNext
        Loop
        rs.Close
        
        'Return the string without the trailing separator.
        lngLen = Len(strOut) - Len(strSeparator)
        If lngLen > 0 Then
            ConcatRelated = Left(strOut, lngLen)
        End If
    
    Exit_Handler:
        'Clean up
        Set rsMV = Nothing
        Set rs = Nothing
        Exit Function
    
    Err_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
        Resume Exit_Handler
    End Function
    
    Function getMaterials() As String
    Dim rs As DAO.Recordset
    Dim strOut As String
    Set rs = DBEngine(0)(0).OpenRecordset("Select * from materials", dbOpenDynaset)
    Do While Not rs.EOF
    strOut = strOut & rs(0) & ", "
    rs.MoveNext
    Loop
    rs.Close
    getMaterials = strOut
    End Function
    

    Reference: 

    http://allenbrowne.com/func-concat.html

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.  

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Salar Music Monday, December 4, 2017 7:12 PM
    Monday, December 4, 2017 6:05 AM
    Moderator