Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2020
    Posts
    2

    Dynamic Query to group transaction dates

    I have set up a database of my bank transactions which also includes 3 credit cards (CC) that I use.
    I wish to set up a query that identifies whether the transaction date falls within the catchment period for the next bill, if it does I want to total those transactions up.

    E.G. If Today is greater than 14th of the current month then the catchment period starts on 15th of previous month; if not then the catchment period starts on 15th 2 months earlier. The catchment period ends on 14th of the month after start. I hope this makes sense.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Assuming that you have a Table named "Transactions" with fields TransDate and Amount, you would use a Totals Query with criteria. The SQL code for that would look like this:
    Code:
    SELECT Sum(Transactions.Amount) AS SumOfAmount
    FROM Transactions
    WHERE (((Transactions.TransDate) Between DateSerial(Year(Date()),IIf(Day(Date())>14,Month(Date())-1,Month(Date())-2),15) And DateSerial(Year(Date()),IIf(Day(Date())>14,Month(Date()),Month(Date())-1),14))) OR (((Transactions.TransDate) Between DateSerial(Year(Date()),IIf(Day(Date())>14,Month(Date())-1,Month(Date())-2),15) And DateSerial(Year(Date()),IIf(Day(Date())>14,Month(Date()),Month(Date())-1),14)));

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you wanted to include fields that show the date range along with the Sum, you can add those calculated fields to the query like this:
    Code:
    SELECT DateSerial(Year(Date()),IIf(Day(Date())>14,Month(Date())-1,Month(Date())-2),15) AS LowerDate, DateSerial(Year(Date()),IIf(Day(Date())>14,Month(Date()),Month(Date())-1),14) AS UpperDate, Sum(Transactions.Amount) AS SumOfAmount
    FROM Transactions
    WHERE (((Transactions.TransDate) Between DateSerial(Year(Date()),IIf(Day(Date())>14,Month(Date())-1,Month(Date())-2),15) And DateSerial(Year(Date()),IIf(Day(Date())>14,Month(Date()),Month(Date())-1),14))) OR (((Transactions.TransDate) Between DateSerial(Year(Date()),IIf(Day(Date())>14,Month(Date())-1,Month(Date())-2),15) And DateSerial(Year(Date()),IIf(Day(Date())>14,Month(Date()),Month(Date())-1),14)));

  4. #4
    Join Date
    Dec 2020
    Posts
    2
    Thanks for the responses guys. This has sorted my inability to produce the results that I desired.

  5. #5
    JoeM is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome.

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

Similar Threads

  1. Query: group by specific dates
    By irish634 in forum Queries
    Replies: 8
    Last Post: 09-13-2019, 07:13 PM
  2. Replies: 8
    Last Post: 04-22-2018, 12:54 PM
  3. Replies: 2
    Last Post: 01-29-2017, 07:50 AM
  4. Replies: 7
    Last Post: 02-10-2016, 10:00 AM
  5. Dynamic dates with query
    By unslog in forum Access
    Replies: 2
    Last Post: 03-23-2012, 07:27 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