Combine Grouping Report Access

[rubenstranger46]

Hi experts...i need help

i have table
invoice_id | code | detail
A-001 | 0001 | Car
A-001 | 0002 | Boat

i want to create report based on that table, grouped by invoice_id and code, but at code group i want to show data like this:

----------------------------------------------
Invoice_id : A-001
----------------------------------------------
Code : 0001 & 0002
----------------------------------------------
Car
Boat
----------------------------------------------

Anyone can help?Thanks a lot

 

[arnelgp]

use allen browne's concatRelated() function to concatenate the "code field" in the query:

select invoice_id, concatRelated("code","yourTableName","invoice_id = '" & [invoice_id] & "'", "", " & ") as code, [detail] from yourTableName;

save the query (query1).
create a report using the query1 as the recordsource of your report.
add grouping: invoice_id
add another grouping: code
the [detail] field should go to the detail section of the report.[/code]

 

[rubenstranger46]

use allen browne's concatRelated() function to concatenate the "code field" in the query:

select invoice_id, concatRelated("code","yourTableName","invoice_id = '" & [invoice_id] & "'", "", " & ") as code, [detail] from yourTableName;

save the query (query1).
create a report using the query1 as the recordsource of your report.
add grouping: invoice_id
add another grouping: code
the [detail] field should go to the detail section of the report.[/code]

thanks a lot..I'll try...

 

[rubenstranger46]

use allen browne's concatRelated() function to concatenate the "code field" in the query:

select invoice_id, concatRelated("code","yourTableName","invoice_id = '" & [invoice_id] & "'", "", " & ") as code, [detail] from yourTableName;

save the query (query1).
create a report using the query1 as the recordsource of your report.
add grouping: invoice_id
add another grouping: code
the [detail] field should go to the detail section of the report.[/code]

Hi arnel, i have success do this.but i have new problem. the field "code" show line with same code.
like this:
----------------------------------------------
Invoice_id : A-001
----------------------------------------------
Code : 0001, 0002, 0002
----------------------------------------------
Car
Boat
Boat
----------------------------------------------

how to group the "code" field to become this

----------------------------------------------
Invoice_id : A-001
----------------------------------------------
Code : 0001, 0002
----------------------------------------------
Car
Boat
Boat
----------------------------------------------

thanks before

 

[arnelgp]

create a function in a module that will remove the duplicate Code:

Public Function fnRemoveDup(ByVal pText As String, Optional ByVal pSeparator As String = ", ") As String
    Dim v As Variant
    Dim i As Integer
    Dim sRet As String
    v = Split(pText, pSeparator)
    For i = 0 To UBound(v)
        If InStr(1, sRet & pSeparator, v(i) & pSeparator) <> 0 Then
        Else
            sRet = sRet & v(i) & pSeparator
        End If
    Next
    If Len(sRet) Then
        sRet = Left$(sRet, Len(sRet) - Len(pSeparator))
    End If
    fnRemoveDup = sRet
End Function

edit your query:

select invoice_id, fnRemoveDup(concatRelated("code","yourTableName","invoice_id = '" & [invoice_id] & "'")) as code, [detail] from yourTableName;