Forum Discussion
Apply Rate if between Start Date and End Date
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.
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.
i made Query2 to get two dates to subtract to each other.
then join it with tblLC (on query1).
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:
- it is always best to add Autonumber field to your table(s).
it will come in handy when it comes to building query like the one we created.
autonumber don't necessarily means it is your PK field.
if you already have LCID as Autonumber, then use it in Query2 instead.
the reason i have ID there is because it comes from your sample db that has ID as
pk on tblLC (FeeBetweenDates.accdb)
- Amazing. that works. The Switch statement was ingenious. I did adjust start date and end date ranges I assigned since the end date and start date were the same dates. After i did that it matched the excel spreadsheet. I now need to dump it to my production file. Many thanks Mr. Arnel. Have a good day.