Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011

    Question Unanswered: access2007 query to calculate average for specific time period

    *using Access2007*

    I am trying to create a query that would calculate average of values for a set time period. Now I know you can create monthly, weekly & yearly averages with summary query, but how to do a custom summary for a time period that depends on a value of another field?

    In more detail: I have data of individual weights in one table, and measures from test done to them at another time point.
    for each individual,
    I would need to calculate the average weight for 10 days before the test date (which is different for each individual)

    I have two tables:


    Ind test_date test_result
    A12 20.11.2010 0.05
    B1 21.10.2010 0.01 ...

    Table2: weights:

    Date Ind wght
    19.10.2010 B1 25 kg
    20.10.2010 B1 23 kg
    19.10.2010 A1 27 kg.....

    The query I need would produce a table that has
    one row for each individual per test date&result, and average weight of that individual as measured on max. 10 days before test

    Ind test_date av_wght(date_wght≤(test_date-10)) test_result
    B1 21.10.2010 24kg 0.01

    thanks a lot if you can show me how to, or direct me to resources that show how to do this!

    Cheers, Emma

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    consider exponentail smoothing
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2011

    Question Re: smoothing?!? ...sorry, was mine a stupid question?)

    healdem, thanks for reply, but either i don't understand it, or you didn't understand my question.
    Sorry if I am asking stupid things, or not explaining things clearly! I am new to access and maybe not formulating it clearly.

    I don't want to be forecasting anything,
    just need to calculate average for values within a set time period, 10 days before test date, which is different to all the individuals.
    All the individuals have been weighed (and I do mean physical weight, in kg, not mathematical weighing!) every few days, for a few years. From all that data, I need to pick out the correct datapoints and calculate what the average weight has been for the 10 days before date x, individual specific value in another table .

    If it was the same time period for all individuals, it would be an easy thing to do. I just do not know how to create a query that would allow averaging over a time period that is specific for an individual..

    does this make any sense?

    Thanks so much to anyone who can help at all

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    exponential smoothing is a method by which you can average out a (time) series of data without having to analyse or know all that data.
    the problem is getting the 'right' smoothing factor.

    in essence the forecast is what ever the current result was - (current result - previous result)* the smoothing factor it doesn't matter what the result is (it could be sales, it could be production, temperature or whatever)

    now I know a bit more about what you are trying to do
    I think you need to split this into two queries
    one retrieves the values which should be easy enough
    one does the average

    the average will be somethign like
    SELECT avg([i]MyColumn[/]) AS AvgWeight FROM MyTable
    WHERE MyDateColumn between ([DateDiff,'d',TargetDate,10),TargetDate)
    if you want to supply the TargetDate as a literal then remember to use the hash symbol either side of the literal AND supply the date in mm/dd/yyyy
    WHERE MyDateColumn BETWEEN ('#04/11/2011#','#04/21/2011#')
    or if you want the user to specify the dates
    WHERE MyDateColumn BETWEEN ('#' & [ENTER START DATE] & '#' '#' & [ENTER FINISH DATE] & '#'
    however Im not keen on such usage as its all to easy for the user to screw up the data entry bit. Persoanlly I'd prefer to pull the values from a form which does all the validations (ie ensures its a valid date valid data and so on). pulling the values form a data entry form means you can use the same values for multiple reports or queries without the user having the chance to *&%^* things up and then blame you for their screw ups. if you do go down the route of using a form make certain you display what the time bands are on the report to avoid the inevitable arguments that may occur.
    WHERE [i]MyDateColumn between ([DateDiff,'d',forms!MyFormName!MyDateControl,10),forms!MyFormName!MyDateControl)
    one thing to bear in mind when using the BETWEEN is to make certain you specify the lower value first. Whether thats critical in Access/JET SQL I forget but it can trip you up using others DB's so its a good idea to get into practise now.

    also you need to bear in mind that Access / JET like all other DB's stores date as date time datatypes so depending on how you populated the original sample recording you may have a date problem if there is no time element recorded then you don't need to worry overly much, but if you have a time recorded then you could get one day less results that you expect

    21 April 2011 comes before 21 April 2011 @ 16:25
    so setting the (upper) limit to 21 April 2011 means that 21 April 2011 @ 16:25 would be excluded from the results. if that is a problem the trick is to define the upper limit as 21 April 2011 @ 23:59:59

    as said earlier if you set the date only you don't need to worry over much. so that depends if you used user input or a default value such as now() or date(). now adds the current date AND current time, date() just the current date
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts