Thread: creating another calculation from a sum calculation

1. Registered User
Join Date
Jan 2004
Posts
37

Unanswered: creating another calculation from a sum calculation

Can the results of a "SUM" calculation be used to perform another calculation in an Access report?

I use the following to determine the % of current paid of the current owed.

=iif(isnull([CURRENT PAID]) or isnull([CURRENT OWED]),null,iif([CURRENT PAID]=0 or [CURRENT OWED] = 0,0,100*[CURRENT PAID]/[CURRENT OWED]))
========================================
The current paid and the current owed fields are totaled for each worker.
This is what is used to find the sum of all their cases where there is current paid and current owed.

=Sum([CURRENT PAID]) or =Sum([CURRENT OWED])

Can these formulas for SUM be used to figure the percent of the sum of current paid to the sum of the current owed? Could you give me some idea of how it would look. Thanks

2. Registered User
Join Date
Feb 2004
Location
Wales
Posts
343
to work out the % for current paid to current owed

=[current paid] / [current owed]*100 = %

of course use the IIF statement to trap nulls

hope this is a quick fix for you

regards
garerth

3. Registered User
Join Date
Jan 2004
Posts
37
Thanks, I had that solved. Now I have this problem:

I’m trying to get the % of the sum arrears paid BY the sum arrears owed and this is the formula that I have:

=IIf(IsNull([ARREARS PAID]) Or IsNull([ARREARS OWED]) Or Null,IIf([ARREARS PAID]=0 Or [ARREARS OWED]=0,0,100*=Sum([ARREARS Paid])/ Sum([ARREARS Owed]))

But I get this error:

THE EXPRESSION YOU ENTERED CONTAINS INVALID SYNTAX
YOU MAY HAVE ENTERED A COMMA WITHOUT A PRECEDING VALUE OR IDENTIFIER

I need to take into account records with a NULL field or field with \$0.00

Can someone give me some ideas on how to fix this please? Thanks

4. Registered User
Join Date
Feb 2004
Location
Wales
Posts
343
Hi

i looked at that query - check out the ( )

you have 6 facing right and only 4 facing left - they should be equal

regards

gareth

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•