Results 1 to 2 of 2
  1. #1
    chastings is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    1

    Retrieving % from Table based on date range from different table

    I have a table that has a date range and a percentage called Underlying_Percentage. My second table has multiple cost fields and an effective date called MAB_report. I need to get the percentage from table Underlying_Percentage based on the effective_date in table MAB_report. If I can get the percentage to pull over I can get the multiplication part. I was thinking of a Dlookup but I need to make sure that if the effective date is between the range the correct percentage pulls.




    Underlying_Percentage Table
    Date_Range Percentage_Amount
    01/01/2015 .03%
    05/01/2016 .08%
    01/01/2017 .05%

    MAB_report Table
    Effective_Date Underlying_Percentage Premium_Paid Total_Underlying_Premium
    02/10/2015 .03% $100.00 =$103
    05/10/2017 .05% $100.00 =$105


    Thanks
    Chad

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A date range requires two dates (begin and end), I see only one date in each record of Underlying_Percentage table. The following query can provide start and end dates:

    SELECT Nz((SELECT Max(Dupe.[Date_Range]) AS MDR FROM Underlying_Percentage AS Dupe WHERE Dupe.Date_Range < Underlying_Percentage.Date_Range),#1/1/2000#)
    AS StartDate, Underlying_Percentage.Date_Range AS EndDate, Underlying_Percentage.Percentage_Amount
    FROM Underlying_Percentage;

    Now use that dataset for the DLookup():
    DLookUp("Percentage_Amount", "Query1", "#" & [Effective_Date] & "# BETWEEN [StartDate] AND [EndDate]")

    Or try:

    SELECT MAB_report.Effective_Date, MAB_report.Premium_Paid, Percentage_Amount FROM Query1, MAB_report WHERE [Effective_Date] BETWEEN [StartDate] AND [EndDate];


    You show .03% but I think you really mean 3%.
    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.

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

Similar Threads

  1. Replies: 2
    Last Post: 03-09-2016, 12:39 PM
  2. Replies: 7
    Last Post: 09-15-2014, 11:55 AM
  3. How create a table for a date range report
    By jegupta in forum Programming
    Replies: 12
    Last Post: 01-30-2014, 12:33 PM
  4. Replies: 1
    Last Post: 05-25-2010, 02:58 PM
  5. Retrieving Week of Year in a date range
    By Korvega in forum Queries
    Replies: 2
    Last Post: 05-27-2006, 06:29 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