435,595 Members | 3,721 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,595 IT Pros & Developers. It's quick & easy.

Need to Average Grand Total of 14 fields

 P: n/a 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 Nov 12 '05 #1
3 Replies

 P: n/a 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. Nov 12 '05 #2

 P: n/a 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" 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 Nov 12 '05 #3

 P: n/a 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 wrote in message news:... 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. Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion. 