Solved

How to get the average number for last 50 days

Posted on 2013-01-05
15
475 Views
Last Modified: 2013-01-07
Dear all experts,

I've a table as follow:

Date   Value
-------------------
01/01    100
02/01    130
03/01    119
04/01    219
    :           :
20/12     399
21/12     111
    :           :
30/12     333
31/12     411


I would like to get the average value (or Maximum or Minimum value) for the last 50 days, is it possible to accomplish in Access 2007?

Thanks in advance
0
Comment
Question by:towo2002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
15 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38746885
Create a query with this:
Select Avg([Value]) As AverageOfValue From TableName Where [Date] > Date()-50
0
 
LVL 1

Author Comment

by:towo2002
ID: 38746891
Dear IrogSinta,

Thanks for your quick reply.  As some of the date are not exist in the table.  How about getting average value for the last 50 records?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38746900
For all three:
Select Max([Value]) As MaximumValue, Min([Value]) As MinimumValue, Avg([Value]) As AverageValue From TableName Where [Date] > Date()-50

I recommend changing the name of your date field since Date is a reserved word.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 29

Expert Comment

by:IrogSinta
ID: 38746954
Do you have a primary key field that is set to Autonumber?
0
 
LVL 1

Author Comment

by:towo2002
ID: 38746988
Hi Irogsinta,

Sorry there is no Autonumber.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38747058
To get the average number over a date span where your table does not contain all of the days, you need to create a query that will generate all of the additional days for you.  To do this, I create a separate table (tbl_Numbers) with a single field (lng_number) and 10 records (with the values 0-9).  From this table, I can then create a query (qry_Number) that generates any series of numbers from 0 to n.  In your case, you only need 0-50, so it would look like:

SELECT Tens.lng_Number * 10 + Ones.lng_Number as lng_Number
FROM tbl_Numbers as Tens, tbl_Numbers as Ones

Save that as qry_Numbers.  Then, to get your average over the last 50 days, you can do the following:

SELECT DateAdd("d", -lng_Number, Date()) as PastDate
FROM qry_Numbers WHERE lng_Number <= 50

This will give you a list of every date from 50 days ago, through todays date.  To incorporate that into your query, you would then use:

SELECT Avg(NZ([Value], 0)) as Average
FROM (SELECT DateAdd("d", -lng_Number, Date()) as PastDate
FROM qry_Numbers WHERE lng_Number <= 50) as Dates
LEFT JOIN yourTable
ON Dates.PastDate = yourTable.[DateField]

By using the subquery to generate a sequence of all the dates, then using a Left Join to join the subquery to your table, you will get a list of Dates and Values, and some of the Values will be NULL.  But you cannot just Average the [Value] field because the NULL values will not be included in that computation, so you must wrap the [Value] field in the NZ() function to convert the NULL values to zero.

HTH
Dale
0
 
LVL 1

Expert Comment

by:jyk_aus
ID: 38749765
I do something similar with exchange rates; I do it by using a recodset of a table. The function that I use allow selection of (Exchage Rates) table,(currency) field and number of records or days as parameters; for sake of simplicity I removde those selections

Here is a simplified version of the function that I use:


+++++
Public Function AveLast50Recs() As Double

'Returns: The avergare exrates for US Dollar for the last 50 valid records


    '---- Declare variables ---
    'General
    Dim strTable As String, tmpSum As Double, AveRate As Double, i As Long, _
    numRecs As Long
   
    'Database
    Dim dbs As DAO.Database
    Dim sTbl As String, rsTbl As DAO.Recordset   'your source table recodset
   
    'MagBox
    Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp As Integer
   
    ' ----Initialise Variable ----
   
    strTable = "ExRates_AUD" 'Repalce with your table name
    AveRate = 0
    msgTitle = "Avarage Calculation"
   
    Set dbs = CurrentDb
   
    '---- Open your Source Table (sorted by date in descending order)
    'Note: [RateDate] is your "Date", [USD] is your "Value"
    sTbl = "SELECT [RateDate],[USD] FROM [" & strTable & "]" _
    & " WHERE [RateDate] <> Null AND Nz([USD],0) <> 0" _
    & " ORDER BY [RateDate] DESC;"
   
    Set rsTbl = dbs.OpenRecordset(sTbl, dbOpenSnapshot)
   
    ' ----- Verify Source table
    If rsTbl.BOF And rsTbl.EOF Then   'No records in table
        msgBtns = vbOKOnly + vbExclamation + vbSystemModal
        msgPmt = "No records in table '" & strTable & "'!"
        MsgBox msgPmt, msgBtns, msgTitle
        GoTo EndExit
    Else
        rsTbl.MoveLast
        numRecs = rsTbl.RecordCount
       
        ' ---- Less than 50 records ----
        If numRecs < 50 Then
            msgBtns = vbYesNo + vbExclamation + vbDefaultButton2 + vbSystemModal
            msgPmt = "There " & IIf(numRecs = 1, "is", "are") & " only " & numRecs & " record" _
            & IIf(numRecs = 1, "", "s") & " in table '" & strTable & "'." & Chr(13) & Chr(13) _
            & "Do you wish to continue anyway?"
            msgResp = MsgBox(msgPmt, msgBtns, msgTitle)
            If msgResp = vbNo Then GoTo EndExit
        End If
    End If
   
    '------ Sum First 50 recs (or all less if no 50 recs)
    tmpSum = 0
    i = 0
    With rsTbl
        .MoveFirst
        Do Until .EOF
            i = i + 1
            tmpSum = tmpSum + ![USD]
            If i = 50 Then Exit Do
            .MoveNext
        Loop
    End With
   
    '-- Caculate average
    If i <> 0 Then 'Avoid dividing by zero
        AveRate = tmpSum / i
    End If
   
   
   
EndExit:

    AveLast50Recs = AveRate
   
   
    '--- Close recodset ---
    rsTbl.Close
    Set rsTbl = Nothing
    Set dbs = Nothing
   

End Function

+++++++

Comment:
I agree with IrogSinta that you should not use "Date" as a field name because it is a reserved word and add that the same applies to a field name "Value" which is also a reserved word.

Best regards
Jacob
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 350 total points
ID: 38751688
SELECT Avg([Value]) as Average
FROM (
    SELECT TOP 50 [Date], [Value]
    FROM tablename
    ORDER BY [Date] DESC
) AS derived
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38751732
Scott's answer (above) will give you the average of the last 50 values, but that is not what you initially asked for, and this is not really a valid number if you are going to extend this across all dates for some sort of other analysis.  If you are going to do that, you need to get a true average, which will involve generating zeros for days where there is no record, which the technique described in my post will do.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38751768
fyed:

Did you see the author's second post?


Dear IrogSinta,

Thanks for your quick reply.  As some of the date are not exist in the table.  How about getting average value for the last 50 records?
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38751881
Scott,

Yes.  I saw it, just wanted the PO to understand that if they want to use that number for any analytic purpose, use of the "average for the last 50 records", is of limited "analytic validity".

Those who don't understand "data analysis" might not understand the difference.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38752086
It could, it depends on the specific purpose of the request and application of the results.  The last 50 results might be functionally equilavent for their current purposes.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38752211
Scott,

Agree, if for example the reason they don't have data for all dates is that the data is for a store that is only open 5 days a week.  If that is the case, then the average of the last 50 records is much more valid.

Just trying to help the OP think thru this and make the correct assessment.

Dale
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38752337
Agreed.

It could be that the data is not right if they just use the last 50 values.

It depends on their specific requirements, and I don't think we have enough details to know which is right :-) .
0
 
LVL 1

Author Closing Comment

by:towo2002
ID: 38753319
Thanks all for the reply.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question