Access Query - Total Calculation

[Ali Xihuny]

Previously, I had a problem calculating total using rates at different periods. But, I found a perfect solution for that from this site.
Here is the link to my previous post and the solution for that problem. http://www.eileenslounge.com/viewtopic.php?f=29&t=15946

Now it got a little bit complicated. I have attached a picture and the database with this link. Hope it will give an idea about what my problem is.

[HansV]

I'm sorry, I don't understand the setup.
Can you explain how the following values should be used:

BO = Single
Room = 2
Adult = 2
Child = 1
Infant = 0

[Ali Xihuny]

In the ratesheet table there is a rate for each adult, child and infant.
BO (Bed Only) type will be Single, Double, Triple or Quad. It will be selected from the BO Combobox.

A_Single_BO is Rate for each adult
C_BO is Rate for each child
I_BO is Rate for each infant

For example;

Arrival: 01-Mar-2014 | Departure: 02-Mar-2014 <--(Rate will be charged for one day)

Rate in the Ratesheet is for one day.

A_Single_BO = 100
C_BO = 50
I_BO = 30

BO = Single
Room = 2
Adult = 2
Child = 1
Infant = 0

Total will be: (((A_Single_BO * Adult) + (C_BO * Child) + (I_BO * Infant)) * Room ) * No.of.Days

If BO = Doube then Total will be: (((A_Double_BO * Adult) + (C_BO * Child) + (I_BO * Infant)) * Room ) * No.of.Days

[HansV]

Thanks. I may have to change the design of the RateSheet table to make it work; that might take a while.

[Ali Xihuny]

Thanks Hans, Hope to hear from you soon. :)

[HansV]

How do you want to handle double, triple and quad rooms? Do you charge a room rate or a per-person rate?

[Ali Xihuny]

If double, triple or quad is selected, then ((room_rate * no.of_adults) + (C_BO + I_BO)) will be charged
There won't be any double, triple or quad rate for Child and Infant.

[HansV]

Do we need single/double/triple/quad at all? Can't we simple use the number of adults?

[Ali Xihuny]

Yes, can't use the number of adults only. Actually, single, double, triple & quad means rate for single bed, double bed, triple bed & quad bed rooms. I hope you get what I'm trying to say.

[HansV]

It's still not quite clear to me. Let's take an example - say that two adults share a room. Do they pay a 'double room' rate, or do they simply pay 2 times the rate for a single adult?

[Ali Xihuny]

If they choose single room, then they pay single room charge. If they choose double room, then they pay double room rate. Or even if they choose quad room they they should pay quad room charge.

[HansV]

That means that we can't multiply the room rate with the number of adults, as in your formula above:

(((A_Double_BO * Adult) + (C_BO * Child) + (I_BO * Infant)) * Room ) * No.of.Days

According to this formula, 2 adults would pay twice the A_Double_BO price!

[Ali Xihuny]

Sorry, my mistake. Room Rate is for each adult.
If two adults choose double room, then room rate is charged for each adult.

[HansV]

I've been looking at the database for quite a while. You're trying to create a complete room reservation system; that's quite complicated and I think it falls outside the scope of a discussion board such as Eileen's Lounge. I would hire a professional Access developer to create it, or buy a ready-made system.

[Ali Xihuny]

Yes, I'm doing an individual project on reservation system. I really need a query which can be used to generate total. That's all I'm asking. Can you please help me? I don't care about the method you use. I just want to make it work. I just can't figure out how to calculate the total & it's the final part of the project. The project is almost 90% completed. Please Help. :(

[HansV]

I'm sorry, it's too complicated - for me at least.

[Ali Xihuny]

Ok, Let's just forget about the single, double, triple, quad, adult, child and infant part. Can you make a total query excluding all those details?

For example, if I select room type then total will be, (no.of days * room_rate)

Here is the query you did last time:

SELECT Room_Type.Property_name, RateSheet.Room_Type, Sum(

(IIf([Forms]![Search]![End_Date]+([Forms]![Search]![Start_Date]<[Forms]![Search]![End_Date])<[End_Date],[Forms]![Search]![End_Date]+([Forms]![Search]![Start_Date]<[Forms]![Search]![End_Date]),[End_Date])-IIf([Forms]![Search]![Start_Date]>[Start_Date],[Forms]![Search]![Start_Date],[Start_Date])+1)*[A_Single_BO] * [Forms]![Search].[adult]

)AS Total
FROM Room_Type INNER JOIN RateSheet ON Room_Type.Room_type=RateSheet.Room_Type
WHERE (((RateSheet.Start_Date)<= [Forms]![Search]![End_Date]+([Forms]![Search]![Start_Date]<[Forms]![Search]![End_Date])) AND((RateSheet.End_Date)>=[Forms]![Search]![Start_Date]))
GROUP BY Room_Type.Property_name, RateSheet.Room_Type;


Can't you use this query to calculate total? I tried this, but I couldn't make the join to work.

[HansV]

For what it's worth, here is my most recent version of the database; I restructured the RateSheet table into RatePeriods and RoomRates and modified the query and form to use them. I removed a lot of stuff, so don't simply copy things into your own version.

Database.zip

[Ali Xihuny]

I really appreciate your help Hans. Thanks a lot. I'll give it a try.