Results 1 to 6 of 6
  1. #1
    samanthaM is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    25

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

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

    Click image for larger version. 

Name:	summarize.jpg 
Views:	24 
Size:	102.3 KB 
ID:	7604

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


    2. Exclude zero values from average calculation

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    This should help
    Code:
    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
    Last edited by orange; 05-13-2012 at 09:32 AM. Reason: typo

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I think you can do this with a query. Something like:
    Code:
    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.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    samanthaM is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    25
    Quote Originally Posted by Bob Fitz View Post
    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.

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    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.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-12-2013, 12:53 AM
  2. Replies: 4
    Last Post: 02-27-2012, 10:29 AM
  3. Replies: 14
    Last Post: 01-10-2012, 03:12 PM
  4. Entry of Large Data Sets into multiple tables
    By bcouzens in forum Access
    Replies: 8
    Last Post: 05-26-2011, 02:22 PM
  5. Avoid jump to first data after insert new picture
    By gigolomoden in forum Programming
    Replies: 0
    Last Post: 08-04-2008, 10:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums