Results 1 to 8 of 8
  1. #1
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121

    sum where dates match between two tables

    I have two tables, one with timesheet dates and one with daily interest. I need a query that will match timesheet date to daily interest dates and sum the daily interest amounts for those dates.
    Here is what I have so far:
    Interest Rate: Sum(IIf([Query4]![Timesheet Date]>=[Daily APR]![MDY],IIf([Query4]![Timesheet Date]<=[Daily APR]![MDY],[Daily APR]![APR]))

    I can't just multiply the Daily Apr by the number of days in the timesheet period since daily APR changes periodically



    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Provide sample data. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    see query 1. The result I would expect in the Interest field is .0060409
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Consider:

    SELECT Timesheet.*, (SELECT Sum(APR) AS SumInt FROM [Daily APR] WHERE [Daily APR].MDY BETWEEN Timesheet.From AND Timesheet.To) AS Interest FROM Timesheet;

    or

    SELECT TIMESHEET.ID, TIMESHEET.from, TIMESHEET.to, DSum("APR","[Daily APR]","MDY BETWEEN #" & [from] & "# AND #" & [to] & "#") AS Interest FROM TIMESHEET;



    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    I can't seem to use DSum in an aggregate query. Is there another way to achieve this using an aggregate query?

  6. #6
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    I posted a stripped down dbase file earlier. If you could edit the query to accomplish this and attach it that would be wonderful.

  7. #7
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    nevermind, I got to it by using the first option you suggested, thank you so much

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    note that From, TO are reserved words and should not be used for field names (they are part of the SQL language) Also it is bad practice to have spaces in names

    However if you create a table called tblNums with a single numeric field called num and populate that field with the values 0 to 9 (more if you need a more than 9 days between your from and to dates)

    then use this query (assuming you have named the field and table as above)

    Code:
    SELECT Sum([DAILY APR].APR) AS SumOfAPR
    FROM [DAILY APR], TIMESHEET, tblNums
    WHERE (((tblNums.num)<=DateDiff("d",[from],[to])) AND (([DAILY APR].mdy)=[from]+[num]));
    it produces .00060409 as your result

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

Similar Threads

  1. Express to match two tables
    By SONCH in forum Queries
    Replies: 1
    Last Post: 05-15-2018, 01:12 AM
  2. Replies: 7
    Last Post: 10-03-2014, 12:31 PM
  3. Query to match dates in two fields
    By shoggy in forum Queries
    Replies: 2
    Last Post: 10-17-2013, 06:20 AM
  4. Match Dates from two tables
    By GabyArco in forum Queries
    Replies: 2
    Last Post: 06-02-2013, 06:03 PM
  5. Cannot match same data in different tables
    By jitendrakalyan in forum Queries
    Replies: 3
    Last Post: 11-22-2010, 12:26 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