Posted on 2012-09-17
Last Modified: 2012-09-18
Here's a sample of the table I have (not actual - dates and values varies):

NUMBER      DESC      DATE                  VALUE
1      DES1      31-Jan-90                        10
1      DES1      28-Feb-90                20
1      DES1      31-Mar-90      30
1      DES1      30-Apr-90                       40
1      DES1      31-May-90      50
1      DES1      30-Jun-90                       60
1      DES2      31-Jan-90                        2
1      DES2      28-Feb-90                        3
1      DES2      31-Mar-90      6
1      DES2      30-Apr-90                        5
1      DES2      31-May-90      4
1      DES2      30-Jun-90                        7
1      DES3      31-Jan-90                      100
1      DES3      28-Feb-90                      101
1      DES3      31-Mar-90      102
1      DES3      30-Apr-90                       105
1      DES3      31-May-90      106
1      DES3      30-Jun-90                        110
2      DES1      31-Jan-90                           1
2      DES1      28-Feb-90                           2
2      DES1      31-Mar-90         3
2      DES1      30-Apr-90                          4
2      DES1      31-May-90         5
2      DES1      30-Jun-90                           6
2      DES2      31-Jan-90                        10
2      DES2      28-Feb-90                        20
2      DES2      31-Mar-90      30
2      DES2      30-Apr-90       40
2      DES2      31-May-90      50
2      DES2      30-Jun-90                        60
2      DES3      31-Jan-90                       101
2      DES3      28-Feb-90                        102
2      DES3      31-Mar-90      103
2      DES3      30-Apr-90                       104
2      DES3      31-May-90      105
2      DES3      30-Jun-90                        106
3      DES1      31-Jan-90                        100
3      DES1      28-Feb-90                         101
3      DES1      31-Mar-90      102
3      DES1      30-Apr-90                        103
3      DES1      31-May-90      104
3      DES1      30-Jun-90                        105
3      DES2      31-Jan-90                         10
3      DES2      28-Feb-90                         20
3      DES2      31-Mar-90        30
3      DES2      30-Apr-90                         40
3      DES2      31-May-90      50
3      DES2      30-Jun-90                        60
3      DES3      31-Jan-90                        2
3      DES3      28-Feb-90                          3
3      DES3      31-Mar-90      4
3      DES3      30-Apr-90                       8
3      DES3      31-May-90      9
3      DES3      30-Jun-90                       10

Here's the table I would like to get from the data thru an Access query:

NUMBER      DESC      SUM      3 MOS AVG        6 MOS AVG
1      DES1                  
1      DES2                  
1      DES3                  
2      DES1                  
2      DES2                  
2      DES3                  
3      DES1                  
3      DES2                  
3      DES3                  

Any help would be greatly appreciated!
Question by:ktjamms2
    LVL 47

    Accepted Solution

    3 month/6 month from when?  Do you mean that you want the Sum and 3 month/6 month averages by Num and Desc going back 3 or 6 months from todays date?  Then what goes in the Sum column, is that the total sum regardless of date?  If so, I think the following could work:

    SELECT Number, Desc, Sum(Value) as SumOfValue,
                 AVG(IIF([DateField] < DateAdd("m", -3, Date()), NULL, [Value])) as Avg30Day,
                 AVG(IIF([DateField] < DateAdd("m", -6, Date()), NULL, [Value])) as Avg60Day
    FROM yourTable
    GROUP BY Number, Desc

    Author Comment

    sum since first date, 3 mos average of first 3 mos dates, 6 mos average of first 6 mos dates
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    first 3 months/6 months from when?

    Do you mean you want to identify the earliest date associated with each Number/Description, and then compute the averages based upon the next 3/6 months?

    Or do you mean the latest (most recent date) associated with each Number/Description?

    Author Comment

    Actually, both would be helpful! Average of earliest date associated with each Number/Description and latest(most recent date) associated with each Number/Description.
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    That description just doesn't help much.  I don't have a clue what the "average of earliest date associate with each Number/Description" means.  You have an "earliest" and a "latest", but not an "average of the earliest".

    Tell, you what, take the data that you provided above and put it in a spreadsheet, you don't need all of it if you think it is too much work

    Then Develop what you want the results to actually look like, based on that data.  In each cell of this desired results, add a comment to the cell that identifies which rows/values you used to come up with your results.

    Author Comment

    That's the part I'm hung up on too. How can I determine the "earliest date" in the date field for the associated NUMBER/DESCRIPTION...and average the VALUE field for 6 months going forward starting from the earliest date. Or same thing only determining the "latest date" in the date field for the associated Number/Description and finding the average of the VALUE field for 6 months  starting with the "latest date" going backward.

    The data is all monthly data.

    So for example:
    lets say the "earliest date" in the date field for the associated Number/Description is 1/31/2000. If wanted to find a 6 month average I would want  to average the associated values from 1/31/2000 - 6/30/2000 for the associated Number/Description.

    Or on a different occasion, I may need the last 6 months average going backward. Say the "latest date" is 6/30/2012 for the associated Number/Description . I would want an average of the VALUE field where the DATE field is from 6/30/2012 - 1/31/2012.
    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    Ok this really becomes a two step process, which can be rolled into a single query with sub-query.  To start with, you would need to determine the earliest Date (BTW, Date is a reserved word, you should give the field a more descriptive name).

    SELECT Number, Desc, MIN([DateField]) as MinDate
    FROM yourTable
    GROUP BY Number, Desc

    This query would give you the earliest start date, or if you use MAX() the most recent date for each number/Desc combination.  You would then combine that to create your more detailed query, which (I think) would look like:

    SELECT T.Number
               , T.Desc
               , Sum(T.Value) as SumOfValue
               , AVG(IIF(T.[DateField] < DateAdd("m", 3, Sub.MinDate), NULL, [Value])) as Avg3Mon
               , AVG(IIF(T.[DateField] < DateAdd("m", 6, Sub.MinDate), NULL, [Value])) as Avg6Mon
    FROM yourTable as T
    INNER JOIN (SELECT Number, Desc, MIN([DateField]) as MinDate
    FROM yourTable
    GROUP BY Number, Desc) as Sub
    ON T.Number = Sub.Number
    AND T.Desc = Sub.Desc
    GROUP BY Number, Desc

    I think that should do it, but it is untested.  The key is that once you have the subquery that identifies the MinDate for each Number/Desc, then join that query to your other table based on the Number and Desc columns, and use the MinDate that was developed in the subQuery as the start date of your 3 month and 6 month periods.

    Author Closing Comment

    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    Glad I could help.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    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…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now