Sum values in one field based on date range in another

Question

I have two tables, both with dates (which is what they are/will be joined on)

Table 1 has daily rainfall values

Table 2 has weekly water volume values.

I'm trying to get Access to calculate the weekly rainfall (from the daily values) based on the dates that are given for the water volumes ie: total rainfall between two dates calculated dynamically. I have some experience with using Access SQL but I am stumped on this one. To add to the complication, every once in a while the volume values aren't always 7 days apart.

Answer 1

One method is to find the week numbers from each table, then join between these:

Select 
    Sum(Table1.Volume) As Volume1, 
    ISO_Weeknumber(Table1.[datefield]) As WeekNumber1, 
    Sum(Table2.Volume) As Volume2, 
    ISO_Weeknumber(Table2.[datefield]) As WeekNumber2 
From 
    Table1
Inner Join
    Table2
    On ISO_Weeknumber(Table1.[datefield])=ISO_Weeknumber(Table2.[datefield])
Group By 
    ISO_Weeknumber(Table1.[datefield]),
    ISO_Weeknumber(Table2.[datefield])

using this function:

Public Function ISO_WeekYearNumber( _
  ByVal datDate As Date, _
  Optional ByRef intYear As Integer, _
  Optional ByRef bytWeek As Byte) _
  As String

' Calculates and returns year and week number for date datDate according to the ISO 8601:1988 standard.
' Optionally returns numeric year and week.
' 1998-2007, Gustav Brock, Cactus Data ApS, CPH.
' May be freely used and distributed.

  Const cbytFirstWeekOfAnyYear  As Byte = 1
  Const cbytLastWeekOfLeapYear  As Byte = 53
  Const cbytMonthJanuary        As Byte = 1
  Const cbytMonthDecember       As Byte = 12
  Const cstrSeparatorYearWeek   As String = "W"

  Dim bytMonth                  As Byte
  Dim bytISOThursday            As Byte
  Dim datLastDayOfYear          As Date

  intYear = Year(datDate)
  bytMonth = Month(datDate)
  bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)

  If bytWeek = cbytLastWeekOfLeapYear Then
    bytISOThursday = Weekday(vbThursday, vbMonday)
    datLastDayOfYear = DateSerial(intYear, cbytMonthDecember, 31)
    If Weekday(datLastDayOfYear, vbMonday) >= bytISOThursday Then
      ' OK, week count of 53 is caused by leap year.
    Else
      ' Correct for Access97/2000+ bug.
      bytWeek = cbytFirstWeekOfAnyYear
    End If
  End If

  ' Adjust year where week number belongs to next or previous year.
  If bytMonth = cbytMonthJanuary Then
    If bytWeek >= cbytLastWeekOfLeapYear - 1 Then
      ' This is an early date of January belonging to the last week of the previous year.
      intYear = intYear - 1
    End If
  ElseIf bytMonth = cbytMonthDecember Then
    If bytWeek = cbytFirstWeekOfAnyYear Then
      ' This is a late date of December belonging to the first week of the next year.
      intYear = intYear + 1
    End If
  End If

  ISO_WeekYearNumber = CStr(intYear) & cstrSeparatorYearWeek & Format(bytWeek, "00")

End Function

For this to work, you must have all weeks in Table1 that exist in Table2 and vice versa.