Rate calculation

[affan]

Dear All,

I hope some of you will be able to help me in calculating the rates over a period of time. I think*the attachment will give you a clear picture of what*I am looking for.
To get the cost for a particular period*I want*the rates to be updated as per the*given period. It would be of great help if I could get the calculation in the mdb format

 

[namliam]

what is your logic for the day/week/month rates? it seems kindoff eratic...
I would solve this case with a simple join and IIF.... I will assume you know how to join the two tables....

Iif(Day(Table2.[Date]<=6, [Day Rate]
  ,Iif(day(table2.[date])>14
     ,[Monthly Rate],[Weekly Rate]
     )
  )

Dont use date or simular words that are reserved words
You are ill adviced for using spaces or special characters in your column names.

 

[Mihail]

It is not possible to answer to your question without to know your DB structure.

From the PDF I can say (but not for sure) that your DB is not normalized.
Database Normalization and Table Structures - Microsoft Access / VBA

Also I can say that yours explanations are wrong and incomplete.

Wrong because you refer to certain days.
I think that what you mean is:
If the selected day is inside the first week of a month then apply rates by day.
If the selected day is inside of the second week of a month then apply rates by week
Finally, if the selected day is greater than the first 2 weeks of the month then apply rates by month.

Incomplete because most of us have no idea how this rates are calculated.

 

[affan]

Dear namliam,
Thanks very much for your quick reply if you have any sample mdb please forward me. that will help me

 

[namliam]

I gave you the code required, what more do you need?