Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    132

    Unanswered: how to run this query?

    There is a table that has recorded daily expenses for over last two years. How can we get a query to only show the sum of expenses of a week on for example every Sunday?
    thank you,

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Please provide information about the table (or tables) involved: name(s), columns names, data types and relationships (if more than one table).
    Have a nice day!

  3. #3
    Join Date
    May 2012
    Posts
    132
    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.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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) :
    Code:
    Sub Make_Tbl_Calendar()
    
        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
        End If
        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)
        Loop
        
    End Sub
    2. Paste this SQL code in a new query (Tbl_Expense is the name I gave to your table):
    Code:
    SELECT   Tbl_Calendar.SysDate, 
             Sum(Tbl_Expense.amount) AS WeeklySumOfExpenses
    FROM     Tbl_Expense, 
             Tbl_Calendar
    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);
    Have a nice day!

  5. #5
    Join Date
    May 2012
    Posts
    132
    thank you,
    you explained it so clearly and i could make it. it was great.
    thank you very much

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •