Results 1 to 3 of 3
  1. #1
    pmpursley is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    1

    Post Using a Do loop to calculate Average

    Hello,



    I'm new to Access and programming in general. I'm trying to understand loops but I'm not making any progress. I'm trying to write a loop to calculate the average of the past 10 Values including the current Value. This is what I have so far.
    There are also Fields for "Date" and "Time" if that is useful.

    [code]
    Dim index As Integer
    Dim RollAveTotal As Long
    Dim CurrentValue As Variant

    Do
    'There are 4 possible Grades. I only need "Grade 1"
    If Me.Grade = "Grade 1" Then
    'Status will be either "Y" or "N". Only Records with "Y" are to be counted. "N"s should be ignored. The "Current
    If Me.Status = "Y" Then
    RollAveTotal = RollAveTotal + Me.Value
    CurrentValue = Me.Value
    RollingAverage = (RollAveTotal + Value) / 10
    End If
    End If
    index = index + 1
    Loop Until index = 9
    [\code]

    Thanks for any help or advice!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Have you tried building and saving a query object? The query builder can help you create a totals query. You can use the average function in the totals query.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    That code won't work without a recordset.

    Like ItsMe said, build an aggregate GROUP BY (Totals) query to average records. The trick will be limiting to most recent 10 records. The TOP N qualifier might serve. Review: http://allenbrowne.com/subquery-01.html#TopN

    Or build a report with the TOP N query as RecordSource and use the report Grouping & Sorting with aggregate calcs features. This will allow display of detail as well as summary data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. calculate the Average of certain records
    By mazides in forum Queries
    Replies: 1
    Last Post: 10-16-2013, 11:10 AM
  2. Replies: 1
    Last Post: 10-29-2012, 07:49 AM
  3. Calculate the Average
    By Jerseynjphillypa in forum Queries
    Replies: 1
    Last Post: 06-18-2012, 03:26 PM
  4. Replies: 13
    Last Post: 05-28-2010, 11:57 AM
  5. Calculate average in a query
    By srbooth in forum Queries
    Replies: 1
    Last Post: 02-20-2010, 09:41 AM

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