Calculation in a group footer.

[stupesek]

I need a text box that is in a group footer that will call a Function named fSAR. I would like it to be global because I will use it in other reports. The calculation will be based on three records in my [Analysis] table:



I need to use the three entries when [AnalysisName] = 73,74, and 75.

A simplified version of the functions is:

fSAR = [AnalysisName]=75 / SQR(([AnalysisName]= 73 + [AnalysisName] = 74)/2) '75=the result for Sodium, 74=the result for Magnesium, 73=the restult for Calcium

What do I have to declare in the Function line and how?

[pbaldy]

I'm not at all clear on what you're trying to achieve. Does this get you started?

http://www.baldyweb.com/Function.htm

[stupesek]

The equation I need to perform will have 3 variables in it:

1 = the analysis result for Calcium
2 = the analysis result for Magnesium
3 = the analysis result for Sodium

Because I tables are Master/Child ([tblAnalysis]/[tblParameter]) all three of the above variables (Calcium, Magnesium, and Sodium) come from the field in [tblAnalysis]![AnalysisResultNumeric]. I know which analysis they are from the table [tblParameter] which is linked to to [tblAnalysis] through a long integer field named [tblAnalysis]![AnalysisName]. So to make the calculation I need to call the result for the value in [AnalysisResultNumeric] for three records when :

[AnalysisName] = 73 'which tells me the parameter is 'Calcium'
[AnalysisName] = 74 'which tells me the parameter is 'Magnesium'
[AnalysisName] = 75 'which tells me the parameter is 'Sodium'

[stupesek]

I can do the math programing so here is the equation simplified:

fSAR = {Result for Sodium}/({Result for Calcium} + {Result for Magnesium})

All three of the variables I need come from three records from same field in the table [tblAnalysis] called [AnalysisResultNumeric] and are differentiated by the field in [tlbAnalysis] called [AnalysisName] which is a field linking to the table [tblParameter] which gives me all the relevant information for that parameter. (Sodium, Calcium, Magnesium, ect.)

[stupesek]

It is basically making a calculation from 3 records of the same field and there will be many other records in the report. I need to pick the records that give the result for Calcium, Magnesium, and Sodium.

[stupesek]

Once again, how do I choose three records out of a report group to make a calculation in the group footer?

[pbaldy]

I'm sorry, I'm not really understanding the data structure and such. All I can say is you'd have to have some criteria that would let you identify the three records.

[stupesek]

Here are the detail lines for the report:

AnalysisName Result
pH 7.0
Conductivity 1.56
Calcium 78
Magnesium 14
Sodium 53
Potassium 7
Nitrate 0.1
Sulfate 34
Chloride 67
group footer
SAR 8.67 'SAR is a calculation based on the three bold results above.

I need to extract the three result values for Calcium, Magnesium, and Sodium each separate detail lines that use the same field in the underlying table and are differentiated by the AnalysisName field in the same table.

[stupesek]

oops the Result values didn't space out porperly..

[RuralGuy]

Are these different fields (columns) in the same record (row) or are they in different records?

[stupesek]

The AnalysisName is a field in a table that Identifies what parameter is in the Result field in the same table. In my above example the numbers were supposed to be aligned under a Result column. Evidently the posting took out the spaces I added.

Anyhow each row is a record in a table, each column is a field in the same table.

[RuralGuy]

So this is a Report correct? Are you aware that you can have invisible controls on your report? Maybe labeled: txtSodium, txtMagnesium, txtCalcium. Assuming the report looks pretty much as you have described then there would probably be these three invisible controls on each line. Their ControlSource for txtCalcium would be set to something like: IIF([AnalysisName] = "Calcium", [Result],0) and so on. Then summing them in the GroupFooter would give you the values you need for your calculation. Make sense?