dlookup aggregate value in detail report

1
votes

I'm struggling with some aggregations on a particular report. I'm counting employee involvement in certain aspects, grouping by department and employee. In the Department footer, I have 4 aggregates. 1 is the number of records for the department, one is the number of different employees submitting records for that department, one is the department's percentage of records in respect to all departments' submissions.

What I need is the number of employees that submitted records (which I have ) DIVIDED BY the total number of employees in that department.

So I made an unbound text box and I'm attempting to Dcount the number of employees in each department. But it's not working.

I have the control set to 

    =DCount("[Name]","tblEmployees","[Department =' " & [Department] & "' ")

The textbox is showing #error when the report is run.

Here's a pic of the report to demonstrate the grouping.

rpt

1
vbams-access

1 Answers

0
votes

The syntax does not look right, it should be:

=DCount("[Name]","tblEmployees","[Department] ='" & [Department] & "'")

You could also just count Completed or Name, because it seems you have an entry for each line:

=Count([Completed])

Because the count is in the department footer, it will only count for the department.