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 hava 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... :)

[Rabbit]

Two options.

1) Create a calendar table, filter for the dates you're looking for, join that to the rate sheet, and do a sum.

2) Use code to loop through your dates and get a running sum to return.

[zmbd]

Ali Xihuny
Please understand, we can help you with specfic problems; however, we do not normally provide completed code/sql/projects.
If you will post your SQL we can help you out.

If you are very new to Access and Database design, then you really need to start out with some basic tutorials. If you would like I can PM you a set of such and other useful links - just ask and it's yours.