creating a complex summary calculation off of a calculated field 1

[SpaghettiStraps]

i need to create a summary field for a calculated field in restricted view. the calculated field in the subform is itself using a variable to utilize a calculated field in another table (subform).

subform "awards" has a calculated field called "RevisedAmount" -- ([Awards.TotalAward]*[Awards.Probability%])/100.

i've put the value of "RevisedAmount" into a variable.

subform "awards2" has a calculated field called "Award_Amt1" --
([RevisedAmount]*[Awards2.Program%])/100

everything works fine up to this point. but now i need to summarize "Award_Amt1" (respecting the restricted view of the subform) and i can't figure out how to do it.

any help would be appreciated. thanks.

 

[footpad]

SS,

Try defining the calculated field in Awards2 to something like:

code( ( sum( [Awards.TotalAward] ) *
sum( [Awards.Probability% ] ) / 100 ) *
[ Awards2.Program% ] ) / 100[/code]

Note that I broke it into three lines for clarity. You'll actually type it as one line.

This may or may not give you the result you're after. If it doesn't, then you can also try:

code( ( ( sum( [Awards.TotalAward] ) *
[Awards.Probability% ] ) / 100 ) *
[ Awards2.Program% ] ) / 100[/code]

If neither works for the result you're after, then we'll need to take an entirely different approach, such as a crosstab. (Crosstabs summaries as data values, which we can then summarize more easily. However, they're not always needed and are frequently used when all that's needed is a slightly more creative calculation.)

If you think you need a crosstab, post a follow-up and we'll go from there.

Hope this helps...

-- Lance

P.S. It's not a very good idea to use "%" or other punctuation characters as fieldnames. They tend to cause problems at some point. I would consider renaming your "Probability%" and "Program%" fields to "ProbabilityPct" and "ProgramPct" respectively. (Or you can simply drop the "%" entirely.)

 

[SpaghettiStraps]

i played around with what you gave me and this is what ended up working.

([:Grants2:Awards.Total Award]*[:Grants2:Awards.Probability%]/100)*Sum([:Grants2:Awards2.Program%]/100)

thanks so much!