Can't get cumulative Total on report

[Guest]

I am using access2000 and am printing an employee report that shows a variety
of information about an employee. At the top of the report I have a summary
section that shows how much money an employee has charged. I have a
calculated field on the report that sums up the total of the charges for a
particular employee.

This syntax works fine:

=DSum("[amount]","Tbl_accounting","[type] = '" & "Charge" & "' And
[employee_id] = " & [employee_id])

I also want another calculation that totals up all the charges made by all
current employees [current employee = yes] not just a particular employee.

I came up with this syntax:

=DSum("[amount]","Tbl_accounting","[type] = '" & "Charge" & "' And [current
employee] = " & "yes")

This syntax doesn't work. It ignores the last part where I am specifing I
want to include all current employees in my totals. It gives me a grand total
but the totals also include employees with a NO in the [current employee]
field. The current employee field is a YES/NO data type. the TYPE field is
a TEXT field.

Thanks for any help.

 

[Steve Schapel]

Stonewall,

Try it like this...
=DSum("[amount]","Tbl_accounting","[type]='Charge' And [current
employee]=Yes")
or...
=DSum("[amount]","Tbl_accounting","[type]='Charge' And [current
employee]<>0")

.... although this is just really a simplification of what you've already
got, so this may not be the problem. Are you sure the [current
employee] field is in the Tbl_accounting table?

 

[Guest]

No, the current employee field IS NOT in the tbl_accounting table. It is
however, in the query for the report. The current employee field is in the
Employees table. Is that the problem?

Stonewall,

Try it like this...
=DSum("[amount]","Tbl_accounting","[type]='Charge' And [current
employee]=Yes")
or...
=DSum("[amount]","Tbl_accounting","[type]='Charge' And [current
employee]<>0")

.... although this is just really a simplification of what you've already
got, so this may not be the problem. Are you sure the [current
employee] field is in the Tbl_accounting table?

--
Steve Schapel, Microsoft Access MVP

I am using access2000 and am printing an employee report that shows a variety
of information about an employee. At the top of the report I have a summary
section that shows how much money an employee has charged. I have a
calculated field on the report that sums up the total of the charges for a
particular employee.

This syntax works fine:

=DSum("[amount]","Tbl_accounting","[type] = '" & "Charge" & "' And
[employee_id] = " & [employee_id])

I also want another calculation that totals up all the charges made by all
current employees [current employee = yes] not just a particular employee.

I came up with this syntax:

=DSum("[amount]","Tbl_accounting","[type] = '" & "Charge" & "' And [current
employee] = " & "yes")

This syntax doesn't work. It ignores the last part where I am specifing I
want to include all current employees in my totals. It gives me a grand total
but the totals also include employees with a NO in the [current employee]
field. The current employee field is a YES/NO data type. the TYPE field is
a TEXT field.

Thanks for any help.

 

[Steve Schapel]

Stonewall,

Yes, that's the problem. The DSum function is operting on the data in
the Tbl_accounting table, and can only use fields in that table for its
criteria. I guess you've got two choices:
- use a query which includes the Tbl_accounting table and the
Employees table, in which case your expression should work by replacing
the name of Tbl_accounting with the name of the query, for example...
=DSum("[amount]","YourQuery","[type]='Charge' And [current employee]=Yes")
- if the applicable data is already in the report itself, you could
use a Sum function rather than DSum, something like this...
=Sum(IIf([type]='Charge' And [current employee]=Yes,[amount],0))

 

[Guest]

Oh my gosh, the Sum function worked! Thank you.

Stonewall,

Yes, that's the problem. The DSum function is operting on the data in
the Tbl_accounting table, and can only use fields in that table for its
criteria. I guess you've got two choices:
- use a query which includes the Tbl_accounting table and the
Employees table, in which case your expression should work by replacing
the name of Tbl_accounting with the name of the query, for example...
=DSum("[amount]","YourQuery","[type]='Charge' And [current employee]=Yes")
- if the applicable data is already in the report itself, you could
use a Sum function rather than DSum, something like this...
=Sum(IIf([type]='Charge' And [current employee]=Yes,[amount],0))

--
Steve Schapel, Microsoft Access MVP

No, the current employee field IS NOT in the tbl_accounting table. It is
however, in the query for the report. The current employee field is in the
Employees table. Is that the problem?