[Last Call] Learn how to a build a cloud-first strategyRegister Now



Posted on 2012-09-17
Medium Priority
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
  • 5
  • 4
LVL 49

Accepted Solution

Dale Fye earned 2000 total points
ID: 38405831
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

ID: 38405845
sum since first date, 3 mos average of first 3 mos dates, 6 mos average of first 6 mos dates
LVL 49

Expert Comment

by:Dale Fye
ID: 38405906
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?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 38405962
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 49

Expert Comment

by:Dale Fye
ID: 38406272
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

ID: 38406539
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 49

Assisted Solution

by:Dale Fye
Dale Fye earned 2000 total points
ID: 38406886
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:

           , 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

ID: 38409886
LVL 49

Expert Comment

by:Dale Fye
ID: 38412109
Glad I could help.

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

825 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