Forum Discussion
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] and [enddate] that tblPricing uses.
What I want to do is apply the correct tblPricing.[Rate] to tblLC.Amount but only apply the rate for the rate in the date range.
I have a simple example in the attached.
What I am missing is the query that would show the rate and the fee.
Fee calculation: tblLC.Amount*tblPricing.Rate*(EndDate-StartDate/360)
My approach might not be the way an expert would do it.
I have never developed something like this so quite frankly I dont know the best way to approach.
I am open to any suggestions.
I have attached a simple db.
the db opens to the form where I choose the date name and the date updates automatically based on the start date and end date assigned in tblDates.
Let me know if it is not clear.
grateful for the help
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.
- 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. 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.
