Results 1 to 3 of 3
  1. #1
    williamr is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2022
    Posts
    4

    Get Totals for 3 months at a time

    I have a table that has data by month. One row for each month, I have 20 other fields that have dollar amounts, I want to add field#1 for 09/01/22, 08/01/22, and 07/01/22 together ($1218.55). When 10/01/22 is added to the table, I need to drop 07/01/22 and add the amounts for 10/01/22, 09/01/22, and 08/01/22. I don't know if this is possible but if it is thank you so very much!!!!

    WKRJ

    DATE Field#1 Field#2 Field#3
    09/01/22 $250.00 $762.23 $399.22


    08/01/22 $622.90 $876.34 $199.30
    07/01/22. $345.65, $523.44, $836.45.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Qk! Let's try again (it is not midnight anymore for me!).

    This doesn't affect your current task (getting sum of last 3 months) much, but generally a better design will be like (it works better when you need to query various data):
    FirstOfMonth AmountType AmountValue
    07/01/22 Type01 $345.65
    07/01/22 Type02 $523.44
    ...
    07/01/22 Type20 $???.??
    08/01/22 Type01 $622.90
    ...
    08/01/22 Type20 $???.??
    09/01/22 Type01 $250.00
    09/01/22 Type02 $762.23
    09/01/22 Type03 $399.22
    ...
    09/01/22 Type20 $???.??

    To get total of last 3 months (the current one included) for certain amount type you run a query like:
    Code:
    SELECT SUM(AmountValue) FROM YourTable WHERE AmountType = QueriedAmountType AND FirstOfMonth BETWEEN DateSerial(YEAR(Date(),MONTH(Date())-2,1) AND DateSerial(YEAR(Date(),MONTH(Date()),1)
    To get totals of last 3 months (the current one included) for all amount types you run a query like (the column CurrentMonth in result is optional):
    Code:
    SELECT DateSerial(YEAR(Date(),MONTH(Date()),1) AS CurrentMonth, AmountType, SUM(AmountValue) FROM YourTable WHERE FirstOfMonth BETWEEN DateSerial(YEAR(Date(),MONTH(Date())-2,1) AND DateSerial(YEAR(Date(),MONTH(Date()),1) GROUP BY AmountType
    Last edited by ArviLaanemets; 09-13-2022 at 12:10 AM.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    this will get you your first two fields, change names to suit and extend for further fields

    Code:
    SELECT Sum(Fld1) AS SumOfFld1, Sum(Fld2) AS SumOfFld2
    FROM (SELECT TOP 3 tdate, fld1, fld2
    FROM myTable
    ORDER BY tdate DESC)  AS A;
    sincerely hope this is not your actual table design or field names, almost certainly will lead to problems down the line

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Number of months in a specific time range
    By alabamax in forum Modules
    Replies: 5
    Last Post: 02-22-2020, 01:18 PM
  2. Cumulative Totals over a period of time?
    By aellistechsupport in forum Queries
    Replies: 30
    Last Post: 05-01-2015, 08:34 AM
  3. Calculate YTD and Rolling-12 Months Totals?
    By McArthurGDM in forum Access
    Replies: 7
    Last Post: 04-08-2015, 11:37 AM
  4. Replies: 3
    Last Post: 02-23-2014, 02:06 PM
  5. Summing Time is giving me wrong totals.
    By Nuke1096 in forum Access
    Replies: 7
    Last Post: 06-13-2013, 11:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums