MS Access Total Calculation Query

[Ali Xihuny]

I have two tables with one-to-many relationship.

(1) Room_Type &
(2) RateSheet

Room_Type Table (Example)

+-------------+-----------+
|Property_Name| Room_Type |
+-------------+-----------+
|Property 1 | R_Type 1 |
|Property 2 | R_Type 2 |
|Property 3 | R_Type 3 |
+-------------+-----------+

RateSheet Table (Example)

+----------+------------+------------+------+
|Room_Type | Start_Date | End_Date | Rate |
+----------+------------+------------+------+
|R_Type 1 | 01-03-2014 | 10-03-2014 | 100 |
|R_Type 1 | 11-03-2014 | 20-03-2014 | 120 |
|R_Type 1 | 21-03-2014 | 10-03-2014 | 130 |
+----------+------------+------------+------+

For each room type there is one or more rates & rates differ at different periods.

I have a search form which will display a query result of Property Name, Room Type, Total for specific number of days.

In the form I have to enter two dates, Start Date & End Date

For example:

Start Date = 05-03-2014
End Date = 12-03-2014


Now the query should display the result similar to the table below

+---------------+-----------+-------+
| Property_Name | Room_Type | Total |
+---------------+-----------+-------+
| Property 1 | R_Type 1 | 720 |
+---------------+-----------+-------+

How total is calculated?

From 1st to 10th March, the rate is 100 per day.
From 11th to 20th March, the rate is 120 per day.

In the form I've entered 05-03-2014 as start date & 12-03-2014 as end date. So, rate is calculated for (5,6,7,8,9,10 & 11) of March (12th March not included when calculating the rate)

So, 100 is the rate for (5,6,7,8,9 & 10th March) and 120 for 11th March.

Total = (6 x 100) + (1 x 120) = 720

I want to make a query to generate this result.

Please help guys... :)

[HansV]

Welcome to Eileen's Lounge!

Here is the SQL for a query you can use:

SELECT RateSheet.Room_Type, Sum((IIf([Forms]![Search]![End_Date]-1<[End_Date],[Forms]![Search]![End_Date]-1,[End_Date])-IIf([Forms]![Search]![Start_Date]>[Start_Date],[Forms]![Search]![Start_Date],[Start_Date])+1)*[Rate]) AS Total
FROM RateSheet
WHERE (((RateSheet.Start_Date)<=[Forms]![Search]![End_Date]-1) AND ((RateSheet.End_Date)>=[Forms]![Search]![Start_Date]))
GROUP BY RateSheet.Room_Type;

I have attached a small sample database.

RoomRates.zip

[Ali Xihuny]

Thanks a lot sir. It worked. But I have one little problem.

When I enter the same date as Start Date & End Date the total result is zero. But, even if the date is same, it should calculate total for one day. How can I modify the query to achieve this?

[HansV]

Sorry about that. you're correct. Here is a new version:

RoomRates.zip

[Ali Xihuny]

Thanks again sir for the quick reply. I have been stuck doing this query for the past few days. I was in a hurry & you are the only one who helped. Tons of thanks to you. :D I'll post my question here in this site from now onwards. Hope to get a quick solution for all my problems.

[HansV]

You're welcome!