the table contains 4 fields: ID, expenseItem, amount and date.
we would like the query to show the amount and date. we do not want to see the record for the last two years, as there are many records and it will be confusing. instead, we want the query to show the sum of each week expense on every sunday. i hope i could convey my meaning.
This won't be pretty in terms of execution time, but here's a solution:
1. Create a calendar table and name it Tbl_Calendar (you only need to do this once). Create a new Module in the database, paste this procedure into it, then execute it (F5) :
Const c_SQLCreate = "CREATE TABLE Tbl_Calendar ( SysCounter LONG CONSTRAINT P_K PRIMARY KEY, SysDate DATETIME );"
Const c_SQLInsert As String = "INSERT INTO Tbl_Calendar ( SysCounter, SysDate ) VALUES ( @L, #@D# );"
Dim x As Variant
If DCount("*", "MSysObjects", "Name='Tbl_Calendar'") > 0 Then
CurrentDb.Execute "DROP TABLE Tbl_Calendar;", dbFailOnError
CurrentDb.Execute c_SQLCreate, dbFailOnError
x = #1/1/2005#
Do Until Year(x) > 2019
CurrentDb.Execute Replace(Replace(c_SQLInsert, "@L", CLng(x)), "@D", Format(x, "mm/dd/yyyy")), dbFailOnError
x = DateAdd("d", 1, x)
2. Paste this SQL code in a new query (Tbl_Expense is the name I gave to your table):
Sum(Tbl_Expense.amount) AS WeeklySumOfExpenses
WHERE (((DatePart("w",[SysDate],2))=7) And
((Year([SysDate]) & DatePart("ww",[Sysdate],2))=Year([Date]) & DatePart("ww",[date],2)))
GROUP BY Tbl_Calendar.SysDate, Tbl_Calendar.SysDate
HAVING ((Sum(Tbl_Expense.amount)) Is Not Null);