Results 1 to 6 of 6
  1. #1
    antioch is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    8

    Summing records between date criteria


    In a table, I have quarterly dates and sales figures for each date. I want to add the last twelve months of sales figures for each date. I've tried DSUM, but it puts the sum of the whole field into the record. Any ideas what I could try? Thanks.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    can you clarify - you have quarterly date but want to sum the last 12 months - do you mean the last 4 quarters? or are the monthly figures in another table? And what does 'the sum of the whole field' mean?

  3. #3
    antioch is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    8
    Yes, I have quarterly data like this and

    sales
    3/31/10 $10
    6/30/10 $5
    9/30/10 $15
    12/30/10 $20
    3/31/11 $15

    So, I want to sum the last 4 quarters of $ amounts in a third field so the output would look like this:

    sales last 4 qtr sales
    12/30/10 $20 $50
    3/31/11 $15 $55

  4. #4
    antioch is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    8
    last 4 quarters in that same table.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try something like

    SELECT *, dsum("[Sales]","[myTable]","[Qdate] between #" & dateadd("m",-11,[qdate]) & "# AND #" & [qdate] & "#") AS AnnualSales
    FROM myTable
    ORDER BY [QDate]

    or this which should be faster

    SELECT *, (SELECT Sum([Sales] FROM [myTable] S WHERE [Qdate] between dateadd("m",-11,mytable.[qdate]) AND mytable.[qdate]) AS AnnualSales
    FROM myTable
    ORDER BY [QDate]

  6. #6
    antioch is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    8
    Thanks so much!

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

Similar Threads

  1. show all records when criteria for DATE field is null.
    By Homegrownandy in forum Queries
    Replies: 7
    Last Post: 05-31-2017, 09:29 AM
  2. Replies: 2
    Last Post: 01-19-2016, 06:05 AM
  3. Summing when no records meet criteria
    By clew3 in forum Queries
    Replies: 6
    Last Post: 11-05-2012, 11:37 AM
  4. Replies: 9
    Last Post: 09-22-2012, 02:04 PM
  5. Replies: 2
    Last Post: 04-27-2010, 01:25 PM

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