By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,631 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,631 IT Pros & Developers. It's quick & easy.

Creating a Calendar from Reservations Dates

P: n/a
Mal
Hello.

I have a database that tracks reservations at a campground.
I want to be able to make a calendar type report that shows how many
people are here in given period.

Stored for each reservation are

dteReservationFROM (The date they come in)
dtrReservationTO (The date they leave)

But how to test and report for a specific date???

ie. with
FROM TO
6/1/04 6/5/04
6/3/04 6/4/04
6/2/04 6/7/04

Somehow report
June 1 = 1 camper
June 2 = 2 campers
June 3 = 3 campers
June 4 = 2 campers (since the one is leaving this day)
June 5 = 1 camper
June 6 = 1 camper
June 7 = 0 campers

I have created a table of dates (single field, 1 for each day this
year) thinking I would somehow test against this....but that's where I
ground to a halt.

Any ideas greatly appreciated.

Mal.
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
fp
Do a search on this ng for reservations database and you will find all kinds
of suggestions.

Here is my suggestion. Use a start date and duration for each reservation.
You can then construct a query that will give you how people are there at
any given time as well as future reservations and you will not have to spend
your time creating a bunch of dates with no reservations. You will have to
decide if the last day is included or excluded and make the appropriate
calculation.

--
******************************
Fred Parker
Lynn Consulting Group, L.L.C.
http://www.lynnconsultinggroup.com
******************************
Nov 12 '05 #2

P: n/a
Two possible approaches.

Method 1: Store each date
If you always book for a whole day, it may be simplest to programmatically
create a record for each date in the range, instead of storing a date range.
Use an unbound form to accept the camper's details and the date range, and
then use code to create a record for each day. This makes it very simple and
fast to query the bookings for any date.

Method 2: Use a Cartesian product to get each date
If you prefer to store the date range, the actual dates have to come from
somewhere, so you need a table of all the possible dates. It's very simple
to create this table with all the dates in the next 50 years if you wish.
You can then create a query containing this table and the reservation table
with *no* join between them. Set the Criteria under tblDate.TheDate to:
Between dteReservationFROM And dteReservationTO
The query then generates a record for each date in the booking.
From there, you can count the bookings for a date etc.

Naturally, method 2 takes longer to calculate. You may be surprised how
quickly this recordset becomes large. For example, if you have places for
250 campers on any date, x 365 days in a year = almost 100k records per
year.
Code to create records for the dates for 50 years:
Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2000# To #12/31/2049#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mal" <ma******@hotmail.com> wrote in message
news:75**************************@posting.google.c om...

I have a database that tracks reservations at a campground.
I want to be able to make a calendar type report that shows how many
people are here in given period.

Stored for each reservation are

dteReservationFROM (The date they come in)
dtrReservationTO (The date they leave)

But how to test and report for a specific date???

ie. with
FROM TO
6/1/04 6/5/04
6/3/04 6/4/04
6/2/04 6/7/04

Somehow report
June 1 = 1 camper
June 2 = 2 campers
June 3 = 3 campers
June 4 = 2 campers (since the one is leaving this day)
June 5 = 1 camper
June 6 = 1 camper
June 7 = 0 campers

I have created a table of dates (single field, 1 for each day this
year) thinking I would somehow test against this....but that's where I
ground to a halt.

Any ideas greatly appreciated.

Mal.

Nov 12 '05 #3

P: n/a
Mal
Allen,
Many thanks...

While it looks much simpler to go with approach NO1, because I am picking up
an established product, Option 2 is the way to go....I will give it a shot
and see what happens.
I have never even thought of having multiple tables in a query and NOT
linking them....seems this would be a great thing in many other situations
too.

Mal.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...
Two possible approaches.

Method 1: Store each date
If you always book for a whole day, it may be simplest to programmatically
create a record for each date in the range, instead of storing a date range. Use an unbound form to accept the camper's details and the date range, and
then use code to create a record for each day. This makes it very simple and fast to query the bookings for any date.

Method 2: Use a Cartesian product to get each date
If you prefer to store the date range, the actual dates have to come from
somewhere, so you need a table of all the possible dates. It's very simple
to create this table with all the dates in the next 50 years if you wish.
You can then create a query containing this table and the reservation table with *no* join between them. Set the Criteria under tblDate.TheDate to:
Between dteReservationFROM And dteReservationTO
The query then generates a record for each date in the booking.
From there, you can count the bookings for a date etc.

Naturally, method 2 takes longer to calculate. You may be surprised how
quickly this recordset becomes large. For example, if you have places for
250 campers on any date, x 365 days in a year = almost 100k records per
year.
Code to create records for the dates for 50 years:
Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2000# To #12/31/2049#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mal" <ma******@hotmail.com> wrote in message
news:75**************************@posting.google.c om...

I have a database that tracks reservations at a campground.
I want to be able to make a calendar type report that shows how many
people are here in given period.

Stored for each reservation are

dteReservationFROM (The date they come in)
dtrReservationTO (The date they leave)

But how to test and report for a specific date???

ie. with
FROM TO
6/1/04 6/5/04
6/3/04 6/4/04
6/2/04 6/7/04

Somehow report
June 1 = 1 camper
June 2 = 2 campers
June 3 = 3 campers
June 4 = 2 campers (since the one is leaving this day)
June 5 = 1 camper
June 6 = 1 camper
June 7 = 0 campers

I have created a table of dates (single field, 1 for each day this
year) thinking I would somehow test against this....but that's where I
ground to a halt.

Any ideas greatly appreciated.

Mal.


Nov 12 '05 #4

P: n/a
Mal
Allen.

I tried your solution at home (access 97) by making the date table as
you specified and then making a dummy reservation table (6 records) in
an otherwise blank database. Everything works well.

Now I try at work (Access 2000) on the real database. I added the
dates (at this point just for this year) and then built the query.
It returns a result for each day of a reservation...but no 'blanks'
for those days with no reservations...I have no idea why as the
structure is the same as I tried at home???

Heres the query SQL.

And ideas?
the ReservationsSimple table has 580 reservations in it and all fields
from that query (not the table) are included here.

Mal.

SELECT tblDate.TheDate, tblReservationsSimple.dtmReservationFrom,
tblReservationsSimple.dtmReservationTo, tblReservationsSimple.intLot,
tblReservationsSimple.lngFCID, *
FROM tblDate, tblReservationsSimple
WHERE (((tblDate.TheDate) Between [dtmReservationFrom] And
DateAdd("d",-1,[dtmReservationTo])));
Nov 12 '05 #5

P: n/a
Hi Mal

Would not expect the query to return the blanks: we have asked it to return
only the days between the booking dates, so it should return a day multiple
times where there are multiple bookings, and not return the date if there
are no bookings.

To add the dates where there are no bookings:
1. Create another query, based in the first query and tblDate.

2. Drag tblDate.TheDate to Query1.TheDate to create a join.

3. Double-click the line joining tblDate and Query1.
Access gives 3 options. Choose:
All records from tblDate, and any matches from Query1.

This is known as an outer join.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mal" <ma******@hotmail.com> wrote in message
news:75**************************@posting.google.c om...
Allen.

I tried your solution at home (access 97) by making the date table as
you specified and then making a dummy reservation table (6 records) in
an otherwise blank database. Everything works well.

Now I try at work (Access 2000) on the real database. I added the
dates (at this point just for this year) and then built the query.
It returns a result for each day of a reservation...but no 'blanks'
for those days with no reservations...I have no idea why as the
structure is the same as I tried at home???

Heres the query SQL.

And ideas?
the ReservationsSimple table has 580 reservations in it and all fields
from that query (not the table) are included here.

Mal.

SELECT tblDate.TheDate, tblReservationsSimple.dtmReservationFrom,
tblReservationsSimple.dtmReservationTo, tblReservationsSimple.intLot,
tblReservationsSimple.lngFCID, *
FROM tblDate, tblReservationsSimple
WHERE (((tblDate.TheDate) Between [dtmReservationFrom] And
DateAdd("d",-1,[dtmReservationTo])));

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.