Access Query - Total Calculation
-
- NewLounger
- Posts: 20
- Joined: 25 Mar 2014, 08:49
Access Query - Total Calculation
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Query - Total Calculation
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
Can you explain how the following values should be used:
BO = Single
Room = 2
Adult = 2
Child = 1
Infant = 0
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 20
- Joined: 25 Mar 2014, 08:49
Re: Access Query - Total Calculation
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
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
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Query - Total Calculation
Thanks. I may have to change the design of the RateSheet table to make it work; that might take a while.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 20
- Joined: 25 Mar 2014, 08:49
Re: Access Query - Total Calculation
Thanks Hans, Hope to hear from you soon. :)
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Query - Total Calculation
How do you want to handle double, triple and quad rooms? Do you charge a room rate or a per-person rate?
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 20
- Joined: 25 Mar 2014, 08:49
Re: Access Query - Total Calculation
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.
There won't be any double, triple or quad rate for Child and Infant.
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Query - Total Calculation
Do we need single/double/triple/quad at all? Can't we simple use the number of adults?
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 20
- Joined: 25 Mar 2014, 08:49
Re: Access Query - Total Calculation
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.
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Query - Total Calculation
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?
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 20
- Joined: 25 Mar 2014, 08:49
Re: Access Query - Total Calculation
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.
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Query - Total Calculation
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!
(((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!
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 20
- Joined: 25 Mar 2014, 08:49
Re: Access Query - Total Calculation
Sorry, my mistake. Room Rate is for each adult.
If two adults choose double room, then room rate is charged for each adult.
If two adults choose double room, then room rate is charged for each adult.
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Query - Total Calculation
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.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 20
- Joined: 25 Mar 2014, 08:49
Re: Access Query - Total Calculation
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. :(
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Query - Total Calculation
I'm sorry, it's too complicated - for me at least.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 20
- Joined: 25 Mar 2014, 08:49
Re: Access Query - Total Calculation
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.
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.
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Access Query - Total Calculation
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 20
- Joined: 25 Mar 2014, 08:49
Re: Access Query - Total Calculation
I really appreciate your help Hans. Thanks a lot. I'll give it a try.