Determining Weekend & Weekdays & Their prices

  • Thread starter Thread starter TC
  • Start date Start date
T

TC

Write the following public function in a module:

(untested)

Public Function TotalPrice (dStart as date, dEnd as date) as currency
dim d as date, tot as currency
for d = dStart to dEnd
if d <is weekday> then
tot = tot + <weekday rate>
else
tot = tot + <weekend rate>
endif
next
TotalPrice = tot
End Sub

Then you could use that function in queries.

You will just have to fix the bits in <...>s. For example, there are lots of
date-related routines which will tell you the day name of any date. Check
online help for more information.

HTH,
TC
 
Surely you should explicitly identify the primary keys! (obvious as they
might be)

TC


PC Datasheet said:
Karen,

While your database is a work in progress, you ought to consider changing your
tables ---

TblRoomType
RoomTypeID
RoomType
WeekDayRate
WeekEndRate

TblRoom
RoomID
RoomNum
RoomTypeID

TblRoomBooking
RoomBookingID
RoomID
StartDate
EndDate

Next Put These two functions in a standard module:

Function CountWeekDays(FirstDay,LastDay) As Integer
Dim BookingDay As Date
CountWeekDays = 0
BookingDay = FirstDay
Do Until BookingDay = LastDay
If WeekDay(BookingDay) <> vbSaturday And WeekDay(BookingDay) <> VbSunday
Then
CountWeekDays = CountWeekDays + 1
End If
BookingDay = BookingDay + 1
Loop
End Function

Function CountWeekEndDays(FirstDay,LastDay) As Integer
Dim BookingDay As Date
CountWeekEndDays = 0
BookingDay = FirstDay
Do Until BookingDay = LastDay
If WeekDay(BookingDay) = vbSaturday Or WeekDay(BookingDay) = VbSunday Then
CountWeekEndDays = CountWeekEndDays + 1
End If
BookingDay = BookingDay + 1
Loop
End Function

Next create a query that joins the three tables and includes the fields,
RoomNum, StartDate and EndDate. In the first empty field in the query put:
NumWeekDays:CountWeekDays(StartDate,EndDate)

In the next empty field put:
NumWeekEndDays:CountWeekEndDays(StartDate,EndDate)

In the next empty field put:
WeekDayRevenue:[NumWeekDays]*[WeekDayRate]

In the next empty field put:
WeekEndRevenue:[NumWeekEndDays]*[WeekEndyRate]

In the next empty field put:
TotalRevenue:[WeekDayRevenue]+[WeekEndRevenue]

You can now use this query in a variety of ways. For instance you could get the
total revenue for a room for a month or you could get the total revenue for a
group of rooms for a month.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com




Karen Huynh said:
Hi
I was wondering if it is possible for access to determine if the nights the
customer's staying are weekdays or weekends. I have two fields in the table
that have StartDate, EndDate and Rmtype that is linked with another table
which has Rmtype, WeekendPrice and Wkdayprice.

I would like access to recognise the days that fall between the StartDate
and EndDate and calculate a subtotal.

eg. Customer's StartDate is 12/10/2003 and EndDate is 15/10/2003. The Rmtype
is "Family" and the WkendPrice is $70 and WkdayPrice is $90.
I want access to realise that 12/10/2003 is Sunday so it charges the
customer wkendprice of $70 then the following 2 days (15th is the day guest
checks out so it doesn't get counted) is $90. This will appear in a textbox
"RoomCost" in the form "invoices"

I hope i've explained myself properly.

If anyone can help, that would be greatly appreciated.
Thank you in advance
Click to expand...
Click to expand...
 
Hi
I was wondering if it is possible for access to determine if the nights the
customer's staying are weekdays or weekends. I have two fields in the table
that have StartDate, EndDate and Rmtype that is linked with another table
which has Rmtype, WeekendPrice and Wkdayprice.

I would like access to recognise the days that fall between the StartDate
and EndDate and calculate a subtotal.

eg. Customer's StartDate is 12/10/2003 and EndDate is 15/10/2003. The Rmtype
is "Family" and the WkendPrice is $70 and WkdayPrice is $90.
I want access to realise that 12/10/2003 is Sunday so it charges the
customer wkendprice of $70 then the following 2 days (15th is the day guest
checks out so it doesn't get counted) is $90. This will appear in a textbox
"RoomCost" in the form "invoices"

I hope i've explained myself properly.

If anyone can help, that would be greatly appreciated.
Thank you in advance
 
Karen Huynh said:
Hi
I was wondering if it is possible for access to determine if the
nights the customer's staying are weekdays or weekends. I have two
fields in the table that have StartDate, EndDate and Rmtype that is
linked with another table which has Rmtype, WeekendPrice and
Wkdayprice.

I would like access to recognise the days that fall between the
StartDate and EndDate and calculate a subtotal.

eg. Customer's StartDate is 12/10/2003 and EndDate is 15/10/2003. The
Rmtype is "Family" and the WkendPrice is $70 and WkdayPrice is $90.
I want access to realise that 12/10/2003 is Sunday so it charges the
customer wkendprice of $70 then the following 2 days (15th is the day
guest checks out so it doesn't get counted) is $90. This will appear
in a textbox "RoomCost" in the form "invoices"

I hope i've explained myself properly.

If anyone can help, that would be greatly appreciated.
Thank you in advance
Click to expand...

There are VBA functions for doing workday math posted at:

http://www.mvps.org/access/datetime/date0012.htm

Among them is a function named dhCountWorkdaysA that returns the number
of non-weekend (and optionally, non-holiday) days between two dates.
I'd suggested you copy the functions from that page and paste them into
a standard module, then use a function like the following (untested
code) to calculate the room price given StartDate, EndDate, WeekendPrice
and Wkdayprice:

'----- start of code -----
Public Function fncRoomPrice( _
StartDate As Date, _
EndDate As Date, _
WeekendPrice As Currency, _
WkdayPrice As Currency) _
As Currency

Dim lngWeekdays As Long

lngWeekdays = dhCountHolidaysA(StartDate, EndDate)

fncRoomPrice = _
(lngWeekdays * WkdayPrice) +
((DateDiff("d", StartDate, EndDate) - lngWeekdays) _
* WeekendPrice)

End Function
'----- end of code -----
 
Hi
Thank you very much for the quick responses. I'll will check the code and
the site out now.

Thank you again :D
Karen
 
(snip)
Reservation
GuestID
ConfirmationNumber (Primary key)
InvoiceNo
RoomNo
CheckInDate
CheckOutDate
ReservationStatus
Click to expand...

If a reservation can be for more than one room, you should not have RoomNo
in the Reservation table. The reserved room numbers should be in a different
"reserved rooms" table, so there can be many rooms per reservation.
Room
RoomNumber (Primary Key)
RoomType (single, double etc)
RoomOccupancyStatus (vacant, occupied, reserved)
RoomCleanStatus (clean, not clean)
RoomSmoke (smoking/non smoking)
ConfirmationNumber
Comments
Click to expand...

You should *definitely* not have ConfirmationNumber in the Room table. Are
you thinking of reserving the room, by plugging the confirmation number into
the room record? You should really do that by creating a new record in the
"reserved rooms" table. Then you can keep a history of room reservations for
statistical or other purposes.
RoomRate
RmType (Primary Key)
WkdayPrice
WkendPrice
RoomPenalty (for late checkouts)
DiscountPercent
Click to expand...

That assumes you only have one discount rate (per room), regardless of guest
or time of year. What if you need different discounts for particular guests
or times of year?

How would you recommend me to change these tables as they appear to be very
similar (except i have included a lot more fields and don't have RoomTypeID
or roomID in the tables.)?
Click to expand...

In table design, "very similar" often means "totally different"! Moving a
single field from one table to another one, can have a large functional
impact. I haven't checked the other respondent's table structures, but they
are almost certainly a good starting point for you to consider.

HTH,
TC
 
Karen,

While your database is a work in progress, you ought to consider changing your
tables ---

TblRoomType
RoomTypeID
RoomType
WeekDayRate
WeekEndRate

TblRoom
RoomID
RoomNum
RoomTypeID

TblRoomBooking
RoomBookingID
RoomID
StartDate
EndDate

Next Put These two functions in a standard module:

Function CountWeekDays(FirstDay,LastDay) As Integer
Dim BookingDay As Date
CountWeekDays = 0
BookingDay = FirstDay
Do Until BookingDay = LastDay
If WeekDay(BookingDay) <> vbSaturday And WeekDay(BookingDay) <> VbSunday
Then
CountWeekDays = CountWeekDays + 1
End If
BookingDay = BookingDay + 1
Loop
End Function

Function CountWeekEndDays(FirstDay,LastDay) As Integer
Dim BookingDay As Date
CountWeekEndDays = 0
BookingDay = FirstDay
Do Until BookingDay = LastDay
If WeekDay(BookingDay) = vbSaturday Or WeekDay(BookingDay) = VbSunday Then
CountWeekEndDays = CountWeekEndDays + 1
End If
BookingDay = BookingDay + 1
Loop
End Function

Next create a query that joins the three tables and includes the fields,
RoomNum, StartDate and EndDate. In the first empty field in the query put:
NumWeekDays:CountWeekDays(StartDate,EndDate)

In the next empty field put:
NumWeekEndDays:CountWeekEndDays(StartDate,EndDate)

In the next empty field put:
WeekDayRevenue:[NumWeekDays]*[WeekDayRate]

In the next empty field put:
WeekEndRevenue:[NumWeekEndDays]*[WeekEndyRate]

In the next empty field put:
TotalRevenue:[WeekDayRevenue]+[WeekEndRevenue]

You can now use this query in a variety of ways. For instance you could get the
total revenue for a room for a month or you could get the total revenue for a
group of rooms for a month.
 
Hi
Thank you for the tip with the tables....I'm a beginner with Access so i've
just been doing simple tables joining them together. I will take your tables
into consideration and thank you for the code too. = )

Also, I was wondering how i could display a list of rooms available between
certain dates.
For example, I have created a form where it has a couple of option groups.
I've worked out that if the user wants to search for a specific room type, a
list of rooms matching that criteria will show. I've also got two text boxes
where the user can enter the Check In and Check Out Dates, but i haven't
quite figured out how to implement this into my search criteria too...

By the way, i have the following tables set up:

Reservation
GuestID
ConfirmationNumber (Primary key)
InvoiceNo
RoomNo
CheckInDate
CheckOutDate
ReservationStatus

Room
RoomNumber (Primary Key)
RoomType (single, double etc)
RoomOccupancyStatus (vacant, occupied, reserved)
RoomCleanStatus (clean, not clean)
RoomSmoke (smoking/non smoking)
ConfirmationNumber
Comments

RoomRate
RmType (Primary Key)
WkdayPrice
WkendPrice
RoomPenalty (for late checkouts)
DiscountPercent

How would you recommend me to change these tables as they appear to be very
similar (except i have included a lot more fields and don't have RoomTypeID
or roomID in the tables.)?

Thank you (once again)



PC Datasheet said:
Karen,

While your database is a work in progress, you ought to consider changing your
tables ---

TblRoomType
RoomTypeID
RoomType
WeekDayRate
WeekEndRate

TblRoom
RoomID
RoomNum
RoomTypeID

TblRoomBooking
RoomBookingID
RoomID
StartDate
EndDate

Next Put These two functions in a standard module:

Function CountWeekDays(FirstDay,LastDay) As Integer
Dim BookingDay As Date
CountWeekDays = 0
BookingDay = FirstDay
Do Until BookingDay = LastDay
If WeekDay(BookingDay) <> vbSaturday And WeekDay(BookingDay) <> VbSunday
Then
CountWeekDays = CountWeekDays + 1
End If
BookingDay = BookingDay + 1
Loop
End Function

Function CountWeekEndDays(FirstDay,LastDay) As Integer
Dim BookingDay As Date
CountWeekEndDays = 0
BookingDay = FirstDay
Do Until BookingDay = LastDay
If WeekDay(BookingDay) = vbSaturday Or WeekDay(BookingDay) = VbSunday Then
CountWeekEndDays = CountWeekEndDays + 1
End If
BookingDay = BookingDay + 1
Loop
End Function

Next create a query that joins the three tables and includes the fields,
RoomNum, StartDate and EndDate. In the first empty field in the query put:
NumWeekDays:CountWeekDays(StartDate,EndDate)

In the next empty field put:
NumWeekEndDays:CountWeekEndDays(StartDate,EndDate)

In the next empty field put:
WeekDayRevenue:[NumWeekDays]*[WeekDayRate]

In the next empty field put:
WeekEndRevenue:[NumWeekEndDays]*[WeekEndyRate]

In the next empty field put:
TotalRevenue:[WeekDayRevenue]+[WeekEndRevenue]

You can now use this query in a variety of ways. For instance you could get the
total revenue for a room for a month or you could get the total revenue for a
group of rooms for a month.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com




Karen Huynh said:
Hi
I was wondering if it is possible for access to determine if the nights the
customer's staying are weekdays or weekends. I have two fields in the table
that have StartDate, EndDate and Rmtype that is linked with another table
which has Rmtype, WeekendPrice and Wkdayprice.

I would like access to recognise the days that fall between the StartDate
and EndDate and calculate a subtotal.

eg. Customer's StartDate is 12/10/2003 and EndDate is 15/10/2003. The Rmtype
is "Family" and the WkendPrice is $70 and WkdayPrice is $90.
I want access to realise that 12/10/2003 is Sunday so it charges the
customer wkendprice of $70 then the following 2 days (15th is the day guest
checks out so it doesn't get counted) is $90. This will appear in a textbox
"RoomCost" in the form "invoices"

I hope i've explained myself properly.

If anyone can help, that would be greatly appreciated.
Thank you in advance
Click to expand...
Click to expand...
 
Karen,

TC made some good points! Let's combine what you have, TC's suggestion and my
original table suggestion and see what we get.

Steve
PC Datasheet

TblGuest
GuestID (PK)
Firstname
LastName
<Something For Children>
<Something For Pets??>
etc etc, etc

TblReservation
ReservationID (PK)
GuestID
ConfirmationNumber
CheckInDate
CheckOutDate
ReservationStatusID

TblReservationStatus
ReservationStatusID
ReservationStatus

TblReservationRoom
ReservationRoomID (PK)
ReservationID
RoomID

TblRoom
RoomID (PK)
RoomNo
RoomTypeID
RoomSmoke (Yes/N0)

TblRoomType
RoomTypeID (PK)
RoomType

TblSeason
SeasonID (PK)
StartDate
EndDate

TblRoomRate
RoomRateID (PK)
RoomID
SeasonID
WeekDayRate
WeekEndRate
DiscountPercent 'verify that this is appropriate here

TblInvoice
InvoiceID (PK)
InvoiceNo
InVoiceDate
ReservationID
RoomPenaltyID

TblRoomPenalty
RoomPenaltyID
RoomPenalityExplanation
RoomPenalty

In the above table you could have something like:
1 1/2 hour $25
2 1 hour $45
3 1 1/2 hour Or More $70

TblInvoiceDetail
InvoiceDetailID (PK)
InvoiceID
ChargeDate
RoomRateID

TblRoomStatus
RoomStatusID (PK)
RoomID
RoomStatusDate
OccupancyStatusID
NeedsCleaned (Yes/No)

TblOccupancyStatus
OccupancyStatusID (PK)
OccupancyStatus



Karen Huynh said:
Hi
Thank you for the tip with the tables....I'm a beginner with Access so i've
just been doing simple tables joining them together. I will take your tables
into consideration and thank you for the code too. = )

Also, I was wondering how i could display a list of rooms available between
certain dates.
For example, I have created a form where it has a couple of option groups.
I've worked out that if the user wants to search for a specific room type, a
list of rooms matching that criteria will show. I've also got two text boxes
where the user can enter the Check In and Check Out Dates, but i haven't
quite figured out how to implement this into my search criteria too...

By the way, i have the following tables set up:

Reservation
GuestID
ConfirmationNumber (Primary key)
InvoiceNo
RoomNo
CheckInDate
CheckOutDate
ReservationStatus

Room
RoomNumber (Primary Key)
RoomType (single, double etc)
RoomOccupancyStatus (vacant, occupied, reserved)
RoomCleanStatus (clean, not clean)
RoomSmoke (smoking/non smoking)
ConfirmationNumber
Comments

RoomRate
RmType (Primary Key)
WkdayPrice
WkendPrice
RoomPenalty (for late checkouts)
DiscountPercent

How would you recommend me to change these tables as they appear to be very
similar (except i have included a lot more fields and don't have RoomTypeID
or roomID in the tables.)?

Thank you (once again)



PC Datasheet said:
Karen,

While your database is a work in progress, you ought to consider changing your
tables ---

TblRoomType
RoomTypeID
RoomType
WeekDayRate
WeekEndRate

TblRoom
RoomID
RoomNum
RoomTypeID

TblRoomBooking
RoomBookingID
RoomID
StartDate
EndDate

Next Put These two functions in a standard module:

Function CountWeekDays(FirstDay,LastDay) As Integer
Dim BookingDay As Date
CountWeekDays = 0
BookingDay = FirstDay
Do Until BookingDay = LastDay
If WeekDay(BookingDay) <> vbSaturday And WeekDay(BookingDay) <> VbSunday
Then
CountWeekDays = CountWeekDays + 1
End If
BookingDay = BookingDay + 1
Loop
End Function

Function CountWeekEndDays(FirstDay,LastDay) As Integer
Dim BookingDay As Date
CountWeekEndDays = 0
BookingDay = FirstDay
Do Until BookingDay = LastDay
If WeekDay(BookingDay) = vbSaturday Or WeekDay(BookingDay) = VbSunday Then
CountWeekEndDays = CountWeekEndDays + 1
End If
BookingDay = BookingDay + 1
Loop
End Function

Next create a query that joins the three tables and includes the fields,
RoomNum, StartDate and EndDate. In the first empty field in the query put:
NumWeekDays:CountWeekDays(StartDate,EndDate)

In the next empty field put:
NumWeekEndDays:CountWeekEndDays(StartDate,EndDate)

In the next empty field put:
WeekDayRevenue:[NumWeekDays]*[WeekDayRate]

In the next empty field put:
WeekEndRevenue:[NumWeekEndDays]*[WeekEndyRate]

In the next empty field put:
TotalRevenue:[WeekDayRevenue]+[WeekEndRevenue]

You can now use this query in a variety of ways. For instance you could get the
total revenue for a room for a month or you could get the total revenue for a
group of rooms for a month.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com




Karen Huynh said:
Hi
I was wondering if it is possible for access to determine if the nights the
customer's staying are weekdays or weekends. I have two fields in the table
that have StartDate, EndDate and Rmtype that is linked with another table
which has Rmtype, WeekendPrice and Wkdayprice.

I would like access to recognise the days that fall between the StartDate
and EndDate and calculate a subtotal.

eg. Customer's StartDate is 12/10/2003 and EndDate is 15/10/2003. The Rmtype
is "Family" and the WkendPrice is $70 and WkdayPrice is $90.
I want access to realise that 12/10/2003 is Sunday so it charges the
customer wkendprice of $70 then the following 2 days (15th is the day guest
checks out so it doesn't get counted) is $90. This will appear in a textbox
"RoomCost" in the form "invoices"

I hope i've explained myself properly.

If anyone can help, that would be greatly appreciated.
Thank you in advance
Click to expand...
Click to expand...
Click to expand...
 
PC Datasheet said:
Karen,

TC made some good points!
:-)


Let's combine what you have, TC's suggestion and my
original table suggestion and see what we get.

Steve
PC Datasheet
(snip)


TblReservation
ReservationID (PK)
GuestID
ConfirmationNumber
CheckInDate
CheckOutDate
ReservationStatusID
Click to expand...

Taking the Devil's Advocate approach: what if they needed different rooms
(in the same reservation) for different periods? (Eg. "hello, I'd like to
book 2 rooms: one for 3 days, and the other for 4".) Maybe checkin/checkout
date is needed at the reservation room level, as well (or instead). That
would also handle the case of: "hi, we need to check out from one of our 2
rooms tomorrow, instead of the day after".)

TblReservationRoom
ReservationRoomID (PK)
ReservationID
RoomID
Click to expand...

Why the seperate PK? I'd use ReservationID +RoomID as a composite PK.

TblRoom
RoomID (PK)
RoomNo
RoomTypeID
RoomSmoke (Yes/N0)
Click to expand...

Again, why the seperate PK? If RoomNo is unique, I would use it as the PK.
If it is not - perhaps because the numbers repeat on every floor - you would
need a floor/room table, no?

Steve, I'm not trying to argue with you here! Just to share suggestions
regarding the OP's problem, and to have some data modelling "what if" fun.

TC
 
Hi!
Thank you once again for some excellent points! I really didn't think that
complex when i first created the database but now i guess i have to do a lot
of rearranging.

Thank you for the tips!

Karen
 
Back
Top