Large Data Tables: Summarizing Data on Single Record [Picture]

[samanthaM]

I am working with a very large data table. And have two issues. See image to clearly illustrate problem.



1. I need to summarize many records, into a single record


2. Exclude zero values from average calculation

[orange]

This should help

Sub yearAvg()

'some test data 6 records
Dim y(5)  As Integer
y(0) = 1960
y(1) = 1971
y(2) = 0
y(3) = 1971
y(4) = 0
y(5) = 1960
' A valid year is a year value that is not blank/empty/0
Dim i As Integer
Dim ValidYearTot As Long            'Sum of Valid Year values
Dim ValidYearCnt As Integer         'Count of Valid Years
For i = 0 To 5

'If the year is NOT blank/empty/0, then include it in calculation
    If y(i) > 0 Then
        ValidYearCnt = ValidYearCnt + 1
        ValidYearTot = ValidYearTot + y(i)
    Else
    'Year is blank/empty/0 so ignore it
    End If
Next i
'determine the avg using only valid years
' Sum of Valid years divided by count of Valid years
MsgBox "Avg Year (excluding blanks) is " & ValidYearTot / ValidYearCnt

End Sub

[Bob Fitz]

I think you can do this with a query. Something like:

SELECT TblName.[Legal_1], Sum(TblName.[Year_Built]) AS YrTot, Count(TblName.[Year_Built]) AS YrTot, [TotNum]/[YrTot] AS Result
FROM TblName
WHERE (((TblName.[Year_Built])>0))
GROUP BY TblName.[Legal_1];

Replace TblName with your table Name.

[samanthaM]

I think you can do this with a query.

Pardon my ignorance, but where do you enter in the custom query code you have written? I am only familiar with simple queries such as query wizard and query by design.

Thank you very much for writing the code for this specific problem, I appreciate it.

[Bob Fitz]

Thank you very much for writing the code for this specific problem, I appreciate it.

The code is untested, so it may not work

Open a new query in design view. Change the view to SQL view. Cut and paste the supplied code. Remember to change the table/query name that is red.

[John_G]

Hi -

This is easily done with a totals query, in query design view.

1) Add the two required fields to your query.
2) Make the query into a Totals query. A New Totals row will be shown, with both fields initially set to "Group By"
3) For the Year_Built field, change Group By to Avg (select from dropdown)
4) In the Criteria field for Year_Built, enter >0 .

That should do it for you - try running the query.

HTH

John