CODEWITHSUNDEEP
sqlms-access
Jolien .A
Jolien .A

Reputation: 173

Ms ACCESS: calculating past annual averages over varying date ranges

In a form on Ms ACCESS, a user can select a commodity (such as copper, nickel, etc.) from a list and a commodity price date from a list. A trailing 12 month average commodity price should then be calculated.

For example: the user selects Copper as commodity and February 1st 2010, 02/01/2010. I then want the average price to be calculated over the time period: [02/01/2009 - 02/01/2010].

I'm not sure how to write this in query form. This is the current incomplete code;

SELECT Avg(CommPrices.Price) AS Expr1, 
FROM CommPrices
WHERE (((CommPrices.Commodity)=[Forms]![Tool Should Cost]![List243]))
AND CommPrices.DateComm = [Forms]![Tool Should Cost]![List55];

List243 is the list of commodities the user can select from, list55 is the list of dates the user can select. All data is obtained from the table CommPrices.

Note: the earliest dates in the column DateComm is 01/01/2008. So if the user selects a date for example 02/01/2008, then calculating the average over the past 12 months before 02/01/2008 won't be possible. I do want the code to still calculate the average using the dates available. (in the example it would just be the average over the past month)

Second Note: the column DateComm only has monthly dates for the first day of every month (e.g 01/01/2008, 02/01/2008, 03/01/2008). The dates listed in list55 can refer to different days in the month (e.g 03/16/2009), in that case I want the code to still calculate the past 12 month average using the closest commodity dates possible. So if the user selects date 03/16/2009, I want the code to calculate the 12 month average for 03/01/2008 - 03/01/2009.

Upvotes: 0

Views: 51

Answers (1)

Gustav
Gustav

Reputation: 55806

For "integer" months it would be:

SELECT 
    Avg(CommPrices.Price) AS AveragePrice, 
FROM 
    CommPrices
WHERE 
    CommPrices.Commodity=[Forms]![Tool Should Cost]![List243]
    AND 
    CommPrices.DateComm = BETWEEN
        DateSerial(Year([Forms]![Tool Should Cost]![List55]) - 1, Month([Forms]![Tool Should Cost]![List55]), 1)
        AND
        DateSerial(Year([Forms]![Tool Should Cost]![List55]), Month([Forms]![Tool Should Cost]![List55]), 1)

Upvotes: 2

Related Questions