Compare date ranges with specific dates when building a query

Question

I have been crushing my head on this problem for three days now maybe you can help me on this one.

So, I have 3 tables.

One called Item Price which contains the prices some items have for a given amount of time.

Basically the table looks like this:

ItemName | Startdate  | Enddate    | Price
-------------------------------------------
Watch    | 01/01/2012 | 04/08/2012 | 180€
Watch    | 05/08/2012 | 31/12/2012 | 150€
Watch    | 01/01/2013 | 31/12/2013 | 200€

The other table is named Sales which contains how many items have been sold during each month.

The table looks like this:

ItemName | SaleMonth  | Number
-----------------------------
Watch    | 01/01/2012 | 93
  .             .        .
  .             .        .
  .             .        .
Watch    | 01/05/2013 | 59
Watch    | 01/06/2013 | 74

And the last one called Exchange Rate with the exchange rate from Euros to Pounds for each Month.

ExchangeMonth | Rate  |
-----------------------
 01/01/2013   | 1.225 | 
 01/02/2013   | 1.166 |

To calculate how much I have gained during a month I use this formula:

Gain_month_i = (ItemPrice_month_i-1 * ItemsSold_month_i-1)/ExchangeRate_month_i

I want to calculate how much I've gained during a given period.

But I can't find a way to obtain the right result if the period I chose is overlapping on different periods in my Item Price table (e.g. if I want to see how much I've gained between the 01/02/2012 and the 01/01/2014)

Answer 1

The essence of your problem is that your [Sales] numbers are aggregated by month, but the price of the item could have changed between the beginning and the end of the month. So, if you sold 50 watches in August 2012 you don't know for certain how many were sold when the price was 180, and how many were sold after the price dropped to 150.

It seems to me that the only reasonable solution would be to use an average monthly price based on the number of days during the month that the various prices were in effect. You could achieve that by using a VBA function like this:

Option Compare Database
Option Explicit

Public Function AverageMonthlyPrice(ItemName As String, AnyDayInMonth As Date) As Variant
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim FirstDayOfMonth As Date, LastDayOfMonth As Date, NumDaysInMonth As Long
Dim IntervalStart As Date, IntervalEnd As Date
Dim WeightedAveragePrice As Variant

WeightedAveragePrice = Null
FirstDayOfMonth = DateSerial(year(AnyDayInMonth), Month(AnyDayInMonth), 1)
LastDayOfMonth = DateAdd("d", -1, DateSerial(year(AnyDayInMonth), Month(AnyDayInMonth) + 1, 1))
NumDaysInMonth = DateDiff("d", FirstDayOfMonth, LastDayOfMonth) + 1
Set qdf = CurrentDb.CreateQueryDef("", _
        "SELECT * FROM [Item Price] " & _
        "WHERE ItemName = [pItemName] " & _
            "AND Enddate >= [pFirstDay] AND Startdate <= [pLastDay]")
qdf!pItemName = ItemName
qdf!pFirstDay = FirstDayOfMonth
qdf!pLastDay = LastDayOfMonth
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
Do While Not rst.EOF
    IntervalStart = IIf(rst!StartDate < FirstDayOfMonth, FirstDayOfMonth, rst!StartDate)
    IntervalEnd = IIf(rst!EndDate > LastDayOfMonth, LastDayOfMonth, rst!EndDate)
    WeightedAveragePrice = Nz(WeightedAveragePrice, 0) + rst!Price * (DateDiff("d", IntervalStart, IntervalEnd) + 1) / NumDaysInMonth
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set qdf = Nothing
If Not IsNull(WeightedAveragePrice) Then
    WeightedAveragePrice = CCur(WeightedAveragePrice)
End If
AverageMonthlyPrice = WeightedAveragePrice
End Function

So now you can run a query in Access that pulls together the quantity sold, the average price, and the exchange rate for each row in your [Sales] table:

SELECT
    Sales.ItemName,
    Sales.SaleMonth,
    Sales.Number,
    AverageMonthlyPrice(Sales.ItemName, Sales.SaleMonth) AS AveragePrice,
    [Exchange Rate].Rate AS ExchangeRate
FROM
    Sales
    INNER JOIN
    [Exchange Rate]
        ON [Exchange Rate].ExchangeMonth = Sales.SaleMonth

...returning something like:

ItemName  SaleMonth   Number  AveragePrice  ExchangeRate
--------  ----------  ------  ------------  ------------
Watch     2012-01-01      93  180                  1.225
Watch     2012-08-01      50  153.871              1.166
Watch     2013-06-01      74  200                  1.234
Watch     2013-05-01      59  200                  1.123

If you save that query in Access then you can use it (just like you would use a table) in other queries to perform the rest of your calculations.