Need to Average Grand Total of 14 fields

[CSDunn]

Hello,
I have 14 fields on a report that hold integer values. The field names
use the following naming convention: T1Number, T2Number ....T14Number.

I need to get a 'sub total' of all fields as follows:

=Sum([T1Number]) ... =Sum([T14Number])

Then I need to get an average of all fields as follows:

(Grand Total of all fields) / (# of fields where Sum of any field <>
0, or IS NOT NULL)

How can I do this?

Thank you for your help!

CSDunn

[Salad]

CSDunn wrote:

Hello,
I have 14 fields on a report that hold integer values. The field names
use the following naming convention: T1Number, T2Number ....T14Number.

I need to get a 'sub total' of all fields as follows:

=Sum([T1Number]) ... =Sum([T14Number])

Then I need to get an average of all fields as follows:

(Grand Total of all fields) / (# of fields where Sum of any field <>
0, or IS NOT NULL)

How can I do this?

Thank you for your help!

CSDunn

Not sure. But I would start off with creating a hidden field that is a
running sum incremented by 1 to get a list of the total record count.

Then I'd create 14 invisible fields that would increment by 1 for each
field value not zero maybe by useing a running sum.

At the end of the record those 14 fields not zero means they had a value
and you could average them.

Or....

You could create another query that uses the same filter and fields of
the report. Ex for column1
Col1 : IIF([Field1] > 0,1,0)
and once you have done that for all 14 fields, make the query a Totals
query and set the Totals row for each column to Sum. You might want to
incorporate the amounts so you have something to calculate on.

Now create a report using this query. THen open up the main report and
drop this report to print at the end.

In your main report, add a column to the recordsource. Ex: Master:"M"
Now make Master your first major group/sort and add a footer. Drop the
sub-report into the footer for Master.

I think this will be much easier to accomplish than a bunch of running sums.

[CDB]

This fairly screams "not-normalized repeating fields...". A normalized data
schema would allow a basic query to give you the answer.

One approach is to use a union query to convert the data into a normalised
form:

SELECT Table1.PK, Table1.T1Number from Table1 WHERE Table1.T1Number IS NOT
NULL
UNION ALL SELECT Table1.PK, Table1.T2Number from Table1 WHERE
Table1.T2Number IS NOT NULL
etc

Then use that query to source an aggregate query to get the grand total and
average.

Zero (0) is a value, and should be permitted only if it is the required
value - it ought not to be used to mean "no value".

(6 salesmen, 15 units sold: average units per salesman: 2.5, even tho' 1
salesman sold none.)

Clive
"CSDunn" <cd***@valverde.edu> wrote in message
news:80**************************@posting.google.c om...

Hello,
I have 14 fields on a report that hold integer values. The field names
use the following naming convention: T1Number, T2Number ....T14Number.

I need to get a 'sub total' of all fields as follows:

=Sum([T1Number]) ... =Sum([T14Number])

Then I need to get an average of all fields as follows:

(Grand Total of all fields) / (# of fields where Sum of any field <>
0, or IS NOT NULL)

How can I do this?

Thank you for your help!

CSDunn

[CSDunn]

Thanks for your help. One thing I am going to try looks something like this:

Option Explicit
Option Compare Text
Private Sub PageFooter_Format(ByRef intCancel As Integer, _
ByRef intFormatCount As Integer)

Dim lngCount As Long
Dim lngNumFields As Long
Dim dblTotal As Double
Dim strFieldName As String

For lngCount = 1 To 14
' strFieldName = "T" & CStr(lngCount) & "Number" ' To sum Fields
' XOR
' strFieldName = "txtT" & CStr(lngCount) & "Number" ' To sum Controls
dblTotal = dblTotal + Nz(Me(strFieldName), 0)
lngNumFields = lngNumFields + IIf(Nz(Me(strFieldName), 0) = 0, 0, 1)
Next lngCount

If lngNumFields > 0 Then
Me.txtTotal = dblTotal / lngNumFields
Else
Me.txtTotal = 0
End If

End Sub
**********************

CSDunn
Salad <oi*@vinegar.com> wrote in message news:<LC******************@newsread2.news.pas.eart hlink.net>...

CSDunn wrote:

Hello,
I have 14 fields on a report that hold integer values. The field names
use the following naming convention: T1Number, T2Number ....T14Number.

I need to get a 'sub total' of all fields as follows:

=Sum([T1Number]) ... =Sum([T14Number])

Then I need to get an average of all fields as follows:

(Grand Total of all fields) / (# of fields where Sum of any field <>
0, or IS NOT NULL)

How can I do this?

Thank you for your help!

CSDunn

Not sure. But I would start off with creating a hidden field that is a
running sum incremented by 1 to get a list of the total record count.

Then I'd create 14 invisible fields that would increment by 1 for each
field value not zero maybe by useing a running sum.

At the end of the record those 14 fields not zero means they had a value
and you could average them.

Or....

You could create another query that uses the same filter and fields of
the report. Ex for column1
Col1 : IIF([Field1] > 0,1,0)
and once you have done that for all 14 fields, make the query a Totals
query and set the Totals row for each column to Sum. You might want to
incorporate the amounts so you have something to calculate on.

Now create a report using this query. THen open up the main report and
drop this report to print at the end.

In your main report, add a column to the recordsource. Ex: Master:"M"
Now make Master your first major group/sort and add a footer. Drop the
sub-report into the footer for Master.

I think this will be much easier to accomplish than a bunch of running sums.