Shape Statement?

[noielen]

I want to view my data report like this:

--------------------------------------------------------------------------
DEPARTMENT1
--------------------------------------------------------------------------
EXTENSION - CALLER - DURATION - CHARGES
822 - EMP1 - 00:12:20 - $5.23
123 - EMP2 - 00:23:12 - $11.25

Subtotal by Department: Department1 $15.48

--------------------------------------------------------------------------
DEPARTMENT2
--------------------------------------------------------------------------
EXTENSION - CALLER - DURATION - CHARGES
111 - EMP13 - 00:12:10 - $1.11
102 - EMP1 - 00:45:13 - $23.15

Subtotal by Department: Department2 $24.26

AND SO ON...

GRAND TOTAL DISPLAY HERE: ....

Database Structure:
Department : Name
Employee : Name, Department, ...
Calls : Caller, Duration, Total, ...

Using Shape statement, i have my code below that gives me wrong output.

cmdrep.CommandText = " SHAPE {Select NAME from Department} " & _
 " AS DEPT APPEND ({SELECT CALLS.EXTENSION, CALLS.CALLER, " & _
" CALLS.DATE, CALLS.TOTAL, CALLS.DURATION, EMPLOYEE.NAME, " & _
" EMPLOYEE.DEPARTMENT " & _
                     " From CALLS, EMPLOYEE WHERE EMPLOYEE.DEPARTMENT = ? " & _
 " AND CALLS.DATE BETWEEN #" & CDate(Me.txtstart.Text) & "# AND #" & _
" CDate(Me.txtend.Text) & "# " & _
                     " } AS EMPCALLS RELATE NAME TO PARAMETER 0, NAME TO " & _
" DEPARTMENT) AS EMPCALLS "

This query displays all departments and all employees as callers which is not relate to the department of employee to department.name....

Any help here really appreciated...

[noielen]

i made it simple, but still won't work:
i changed the the name, department fields in my employee table to caller, departmentname and the name field in my department table to departmentname:

cmdrep.CommandText = " SHAPE  {select * from Department} " & _
                     " APPEND ((SHAPE  {select * from employee} " & _
                     " APPEND ({select * from [calls]} AS rscalls " & _
                     " RELATE caller TO caller)) AS rsempcalls " & _
                     " RELATE departmentname TO departmentname) "

error occured: departmentname does not exist in the appropriate rowset.

[Hack]

Moved to Reporting section

[noielen]

why is it give an error: "Report sections do not match data source"

cmdrep.CommandText = " SHAPE  {select * from department} AS department " & _
                     " APPEND ((SHAPE {select * from employee} " & _
                     " APPEND ({select * from [calls]} as empcalls " & _
                     " RELATE caller TO caller)) " & _
                     " RELATE departmentname TO departmentname) "

see attached file as my design view in my data report.

[noielen]

any idea here please?

[noielen]

continue here please... drop some ideas here