Customized Format for displaying record

[wasim_sono]

Dear All



I need to develope a summary report. For This want to display the records in one line.

For example I have fileds 'Bill No', 'Emp Name'and 'Amount'.
The data for this is as under

002 ABC 2450
003 CBA 1560
004 ABC 623
005 CBA 952
006 ABD 425

the report format may look like

EMP NAME Bill No Total Bill Amount

ABC 002,004 2 3073
CBA 003,005 2 2512
ABD 006 1 425

I need to knoe that how to display bill no in a row?

Thanks.

Wasim

[matthewspatrick]

Wasim,

Add this function to a regular module:

Function ConcatFieldVals(TblName As String, ConcatField As String, GroupField As String, GroupVal)

    Dim rs As DAO.Recordset
    Dim Result As String
    
    Set rs = CurrentDb.OpenRecordset("SELECT [" & ConcatField & "] AS Concat " & _
        "FROM [" & TblName & "] " & _
        "WHERE [" & GroupField & "] = '" & GroupVal & "'")
        
    With rs
        .MoveFirst
        Do Until .EOF
            Result = Result & "," & !Concat
            .MoveNext
        Loop
        .Close
    End With
    
    ConcatFieldVals = Mid(Result, 2)
    
    Set rs = Nothing
    
End Function

Then run a query like this:

SELECT EmpName, ConcatFieldVals("tbl","BillNo","EmpName",[EmpName]) AS Bill, Count(Amount) AS CountOfAmount, Sum(Amount) AS SumOfAmount
FROM tbl
GROUP BY EmpName, ConcatFieldVals("tbl","BillNo","EmpName",[EmpName]);