Average generated for specific records
Hi all, I'm trying to figure out how to calculate an average from specific records in a table and am having trouble determining how to solve this puzzle. The situation is this: I have multiple nitrogen ("N") analysis results for the same livestock site ("SiteID"), but only want the average determined from specific records (and not all from a single site). I created an "Include" field that would serve as a means of selecting the fields I wanted, created a self-join relationship, then wrote the following 'If' statement to determine my average:
If (Manure Analysis Data 2::Include="Yes"; Average ( Manure Analysis Data 2::N );"")
Currently, this calculation gives the me the average of all N values for the SiteID, which is not what I want. I thought about limiting the records to only those that I want, but this database is for a client and I would prefer not to have to add another step to this process.
For the record, I've done something similar to this using a summary report, but I need to be able to use the generated value in a portal; I believe will be especially important when I need to generate several N averages when there are multiple operations ("OperationID") within a "SiteID".
Thoughts/shrieks of terror/dope-slaps are welcome.