Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Jan 31, 2022
Solved

Apply Rate if between Start Date and End Date

Hello Experts I have a table tblLC that has a [date] and an [amount] I have a table tblPricing with a [startdate] and [enddate] and [Rate] I have a table tblDates where I assign the [startdate] an...
access
arnel_gp's avatar
arnel_gp
Iron Contributor
Jan 31, 2022

Tony2021 

 

you only need to put the ID of tblDates to your tblPricing table (see in design view).

i created Query 1 that joins LC table to qryPricing.

note that ms access will complain when you open in design view (query1) because

i am joining using >= and <= operator. 

don't worry, you can view it in SQL view without problem.

run Query1.

  • Tony2021's avatar
    Tony2021
    Iron Contributor
    Feb 03, 2022

    arnel_gp 

    Hello Arnel,

     

    Would you be able to adjust the query to show the fee for each record in tblLC?  

    The qryPricingFeeCalc has 36 records (which is more than the 12 records in tblLC) but what I need is to show the fee for the number of records in tblLC.  The query seems to have tripled the number of records to 36. 

     

    I have attached an excel file as a better representation.  Please let me know if not clear.  Thank you very much. 

     

     

     

     

    • arnel_gp's avatar
      arnel_gp
      Iron Contributor
      Feb 03, 2022

      Tony2021 

      i made Query2 to get two dates to subtract to each other.

      then join it with tblLC (on query1).

      • Tony2021's avatar
        Tony2021
        Iron Contributor
        Feb 05, 2022

        arnel_gp 

        HI Arnel,
        I have a question when you have a sec.
        On query1, I see there is a relationship of ID(pk) to ID.
        However, I do not have an ID in my production db...Only LCID that is primary key.

         

         

        Knowing that my primary key is LCID in tblLC, does this change anything and if so is there a work around for it? I dont think I can add an ID field as pk when I already have an LCID field as PK in tblLC. I dont think I can simply change the relationship from the ID to ID (as it is now) to a new relationship of LCID to ID in query1 (the same number of records display though but the rate changes).  I hope that is not confusing. I tried many ways to modify but at a standstill since I now think I need an ID as PK in and remove the PK property from LCID.  It would be a major modification in my db I think and wondering if instead of using ID as PK n query1 I can use LCID as pk.

         

        Please let me know what you think.   

         

         

        here is my tblLC in my production db: 

         

         

  • Tony2021's avatar
    Tony2021
    Iron Contributor
    Feb 02, 2022
    HI Arnel,
    Sorry for my tardy reply. I have only had a little bit of time to dig into this. I have to adjust it a little for my production db. I only posted a simple db to get the logic down. I will probably have more time in a few days and revert if any questions. I, once again, sincerely appreciate your expert intellect.

Resources