For the last two pairs of match fields, use:
MK_Hotel_CheckOut >= Date_Starting AND
MK_Hotel_CheckIN <= Date_Ending
I used to use a much more complex approach until someone here in this community showed me this simpler set of inequalities.
Thank you for the comment but still i need to find a way to get rates for every single day if possible for the hotel stay period.
I have replied to the specific question that you asked by providing a way to match to the period or periods in question. You'll need to tell me exactly where you still have a problem making this work as I can imagine many different issues that you might have but prefer not to guess.
i can connect the checkin and checkout to the two tables if each one of them is falling in different rate.
but imagine check in is may 1st and checkout is May 20th
for that hotel
rate1 covers from may 1st to may 10th
rate2 covers from may 11th to may 15th
rate3 covers from may 16th to may 25th
how would i do the calculation? and what if the hotel stay falls in 4 or 5 rates not only 3?
i hope this make it clear.
I would set up a related table linked to your reservation record with the start and end dates. These records would each have a different date that represents one day of the reservation. Each can use that single date to look up the rate and the sum of these looked up values then computes your basic room cost.
A looping script can create this set of related records.
You can get list of rates using one relation that philmodjunk wrote.
the 1st rate is applied between check in and end of covered date
middle rates are applied full dates that covers
the last rate is applied between start of covered date and check out