Calculate prices for reservations using query

[mdubravic]

Hi, I have this issue:
I have two excel tables that I linked in access. From excel tables I created query that gives me all data I need.
First table has data of periods and prices of apartment per period. For example:
PropertyID 1111
PropertyName Apartment Zagreb Mila
StaringDateOfPeriod 1.1.2015
EndingDateOfPeriod 1.7.2015
PriceForPeriod 100
.
.
PropertyID 1111
PropertyName Apartment Zagreb Mila
StaringDateOfPeriod 1.7.2015
EndingDateOfPeriod 31.12.2015
PriceForPeriod 120



Now as you can see I have apartments with prices that change per periods.

Second excel table gives me informations of total paid service that customers need to pay , number of days that he will stay and CheckIn and CheckOut dates of those guest.

In calclutated fields in query I created calculations like TotalyPiad(from 2. table) - OwnerPrice*TotalyNumberofDays . I guest good result IF i have reservations that matches or is it BETWEEN StartDate and EndDate of periods.
BUT If I have reservation that is overlaping , where CheckOut of guest is 1 or more days > that EndDate of selected period I have problem with calculation because my calculation is based on WRONG price in that period.

For now what I manage to do is to create separate field in query where I calculated number of days that are out of range

CHECK_IN - StartingDate of period
CHECK_OUT -EndingDate of period
Check-in date - starting date of reservation of guest
Check-out date - ending date of guests reservation
nabavna cijena - owner price

PreklapanjePerioda: IIf([Check-out date]>[CHECK_OUT];[Check-out date]-[CHECK_OUT];Null)*[nabavna cijena]

But still when I calculate those number of days I multiple those number of days with old period.
So HOW CAN I MULTIPLE THOSE NUMBER OF DAYS WITH PERIOD +1 , I meen next period not this old one just for those days that are out of range? [CHECK_OUT] +1 doesn't help it just add extra day in my CHECK_OUT filed

My final idea is : separately calculate in one field number of days that are out site of range
Calculate number of days of reservation of guest that are in range
= make sum of those two fields

From that I will create report which I will send to our owners of properties (number of reservations, reservations details, price that they need to pay to us ...)

I also thought this could be possible to make using VBA but I am still not skilled anough in it. Also I was thinking of creating calendar where I would that add those two queries but I do not have idea how to create simple calendar in Access. Is there some template for simple calendar in Access?

I really appreciate any help
Thank you!

 

[Alfisdad]

Hi mdubravic,

I've attached what I think is the solution to your problem. I'll leave you to understand how it works.

 

[mdubravic]

Hi, thank you very much!!!
That is exactly i need. I am now trying to implement this in my database.
For now this is the greatest help I got on the web.

 

[Alfisdad]

You're welcome. It was an interesting little problem and I was looking for something to do - trouble with working this time of year is no-one else is around so I've not been able to move forward with any of my work!

Glad to be able to help.

 

[mdubravic]

So as I understood your example you created composite key in table so that you can handle same PropertyIds and put them as PrimaryKey.
But what when I got new reservation in my table PropertyUsage? I do not see results in your query.
Thanks!

 

[mdubravic]

I manage to create what you provided and implement it. But still there are some issues that bother me. In this query that you provided to me I have total sum for PropertyId. That is great if I want to present that. But I need to report monthly or weekly something like this:

PropertId 1111
PropertyName Apartment Zagreb
TotalOwnerEarn: TotalMuberofStay*OwnerPeriodPrice
AgencyPrice: TotalyCostForGuest-TotalOwnerEarn

Now bigest problem is that when I run query I have to fileds that are separated and If i have overlapping reservation

Here is my code:

SELECT Sum(IIf([GuestCheckIn]>=[PeriodStart] And   [GuestCheckOut]<=DateAdd("d",1,[PeriodEnd]),DateDiff("d",[GuestCheckIn],[GuestCheckOut])*[nabavna   cijena],IIf([GuestCheckIn]<=[PeriodEnd] And   [GuestCheckOut]>[PeriodEnd],(DateDiff("d",[GuestCheckIn],[PeriodEnd])+1)*[nabavna   cijena],DateDiff("d",[PeriodStart],[GuestCheckOut])*[nabavna  cijena])))  

AS TotalNabavna, TablicaRezervacija.PropertyID, TablicaCijena.provizija,   TablicaRezervacija.[Guest first name], TablicaRezervacija.[Guest last   name], TablicaRezervacija.PropertyName, TablicaCijena.[1 NIGHT],   TablicaCijena.[3 NIGHT], TablicaRezervacija.[Total stay time],   TablicaCijena.PeriodStart, TablicaCijena.PeriodEnd,   TablicaRezervacija.GuestCheckIn, TablicaRezervacija.GuestCheckOut,   TablicaRezervacija.[Guest to Agency total due],   TablicaCijena.ProviderName

FROM TablicaCijena INNER JOIN TablicaRezervacija ON TablicaCijena.PropertyID = TablicaRezervacija.PropertyID
WHERE (((TablicaRezervacija.GuestCheckIn)>=[PeriodStart] And   (TablicaRezervacija.GuestCheckIn)<=[PeriodEnd])) OR   (((TablicaRezervacija.GuestCheckOut)>=[PeriodStart] And   (TablicaRezervacija.GuestCheckOut)<=[PeriodEnd]))
GROUP BY TablicaRezervacija.PropertyID, TablicaCijena.provizija,   TablicaRezervacija.[Guest first name], TablicaRezervacija.[Guest last   name], TablicaRezervacija.PropertyName, TablicaCijena.[1 NIGHT],   TablicaCijena.[3 NIGHT], TablicaRezervacija.[Total stay time],   TablicaCijena.PeriodStart, TablicaCijena.PeriodEnd,   TablicaRezervacija.GuestCheckIn, TablicaRezervacija.GuestCheckOut,   TablicaRezervacija.[Guest to Agency total due],   TablicaCijena.ProviderName
ORDER BY TablicaRezervacija.PropertyID;

 

[mdubravic]

I think I found solution! Thank you!

 

[Alfisdad]

Sorry - only just seen your subsequent posts. Glad you found a solution.

 

[mdubravic]

HI, everything is working but I have different problem now:

as I use third pary system from there I am downloading all my reservations in excel. That excel file I linked to Access and I created CreateTable Query that creates table with all data.

What I want to do is that our sales team can easily download that table, put it into dropbox, run query and get all data for reports.

But when I create new table all my primary keys are gone so I am stuck here.

What is the best way in this situation?

 

[Alfisdad]

Why don't you import the data directly into Access rather than doing it via Excel? How is the data structured that you are importing into Excel?

 

[mdubravic]

Reason why I am not directly importing is that I do not full access to that 3rd party system, and we need some more options that that system doesn't have. I have only date from excel sheet. Actualy we do have API that we can use and import data directly, but I do not how to that in access.

 

[Alfisdad]

Import the excel data into 2 new tables using the Import from Excel wizard in the External Data tab - you will need to run the wizard once for each table. You can then amend the structure of the tables to add the keys if you wish (although I would think that my solution would work without they primary keys).

I would then remove all the data from both tables, and run the wizards again, this time importing directly into the tables. At the end of the Wizard you get the option to save the import steps - this will make it available again via Saved Imports on the External Data tab. When you import the data again it will populate these tables and maintain the keys (but be careful that if the data you are importing has duplicate key values the import will skip these records).