Can anyone help? I'm designing a database for a villa letting system and I need to ensure that the database will mark the dates that I choose booked so that there are no double bookings. How do I do this?

Assuming one booking per day...make the BookingDate a primary key in the table. If you can have multiple bookings per day then it gets more complicated.

Hi
Do not make the Booking date the primary number in the table. The primary key should not consist of data meaningful to the user. As a general rule the PK should be an autonumber field.
Are yo working with multiple villas or a single villa?
If it is a single villa then you could set the index for the date booked to No Duplicates. Multiple villas would be a different proposition.
If you need more help post back
Jim

hi thanks loads for your help.
yeah i'm afraid its 13 villas and many bookings need to be made on one day. Nightmare!!!! You sound like a genius though so could you help me again please!!
thanks

you still having problems
i daily work on a database for a holiday company and can help you alot

Loonatik

Hi
I'm no genius but if you set up your structure right, it should be fairly straightforward.
Have a table for the thirteen Villas:
tblVillas
Villa_ID (Primary Key, AutoNumber)
Villa Name
Address details etc of Villa

tblCustomers
Customer_ID (Primary Key Autonumber)
FName
SName
Address et al

tblBookings
BookingID (PrimaryKey Autonumber)
CustomerID (Number, Long Integer)
VillaID( Number, Long Integer)
Date of Booking
Duration of Booking

Make the Date of booking and VillaID in the tblBookings table an Index (in design view of the table you click on the Idexes tool, Name the Index then list the fields to include in the Index) This will make the combination of the two fields, Unique.

However, this only ensures that a villa cannot be booked twice for the same start day:
Customer A might book Villa 1 for 14 nights on 17/11/03
Therfore villa booked till 01/12/03
Customer B might also book Villa 1 for 7 nights starting 24/11/03
Since the viilaID is the same, but the Date of booking is different, this would be allowed in the index but op course would mean that you are double booked for the last week in November (with two somewhat disgruntled customers )
Some form level validation would probably be the best way to go.
How do you handle it Loonatik (I bow to your experience )
Jim

thanks u lot, your legends!!!! if you know then cen u help me pls lunatic. i want to ensure that no double bookings are made for each of the 13 villas I have. i'd also like to be able to use the Calendar to select dates so that i can choose a start date of a booking as "1/1/04" for example, and then and end date of "7/1/04" and i would like it to select the dates in between.
how bloody complicated, or maybe i'm just a simpleton!!

ri

hi
we are currently using wall boards to show options and bookings however, stick with me because I am just designing an availability form to show if properties are vacant or booked and I will be happy to pass it on to you when it is completed. Anything else you might be interested in? Do you do flights too?

Loonatik

Hi
Check out the attachment for indicating if there is a duplicate booking. If you need more help don't hesitate to ask
Jim

cheers Jim, i'll look over that on Monday. I've managed to find an internet site showing how I can use the Calender in Access using Visual Basic but its not allowing me to do bookings or anything with the dates so i'll leave the calendar out!!
Hey Loonatik, thanks 4 yr help, no i'm not doing flights just booking for the villas and whether they need a transfer or not. could i just have a look at how you've done your availability forms, that would be a great help. When do you hope to be finished by?

Ri

Hey,Sorry to bother u again!! Do you know if I can use the calendar to select dates and put them into some kind of form or table?

ri

Hiya, live just south of the river from you, so I can't joke you're too near. Anyways I am working on roughly the same thing as you except mine is an Apart Hotel, and tonight I have finally managed to do something in Access that works. It isn't exactly what I wanted , and may not be what you want but it will give you an idea of how to go about getting it but at least it does work. Have a try.

cheers yr a legend!! i'm gonna have a butchers today and i'll get back to u this afternoon some time.

ri

hey yeah that was really useful.are u doing yr work for a company?

ri

I've attached the basics of what I've done. I know it might seem long winded but I feel more comfortable with less code that I am not always sure how to amend when things go wrong.
They are currently all showing vacant as I am doing the dates for 2004 and only currently have 2003 dates but if you go to bookings and change the dates to 2004 you will see how they work.

Loonatik

what website did you find? I'm working on a vacation request calendar and need all of the help I can get.

i found it on microsoft msdn site, its quite good. just look up calendar 9.0 or 10.0. i hope thats some help

ria

i can't find the attachment in your message!!!!! could u send it again please.

ri

sorry could u help me out again pls? i must be really peeing u off!!
there are 13 families that own the 13 villas on the same complex and so if a booking is made, the money will be split 13 ways. they therefore want each booking to be made to go to the next villa. for example:

if i make a booking, i will be in villa1, i want the next booking to be in villa 2 and then villa 3 and so on.
do u know if i can get the system to automate that, so it moves on to the next villa?

ri

i have a booking form for villas and i am using a calendar to select the date RANGE however, I need it then to select all dates within that range and if possible highlight them on the calendar. does anyone know how?

ri

I have done mine the other way round. Not selecting by date but selecting by property. So for each property staff can look at the 30 weeks we are operational and see which weeks are free for each property. I am going to set up forms so that you can either select by property or by date to see whats free. This is what I am doing next week and can post as soon as I've completed.

Loonatik

Hi,

Can I see a copy of the structure of your DB. It sounds close to what i'm struggling with.

Thankz in advance,

Herman

i'm doing it!

you need to base your query on the form field of start date or whatever you have called it. When we have bookings we select optioned/booked/cancelled on the booking form.

To query I created a query and in the first line of the query type =IIf([Start Date] Between #01/03/04# and #31/03/04#,[Status],"Vacant") - the query also includes the villa names and codes. I actually have a few dates not just start date, so if you have more than one date field let me know.

Where [status] is the optioned/booked/cancelled field.

Do this for each date selection that you require and make a form from this. I am not using any calendar feature

Loonatik

thanks that might help. but does your system only have the option of selecting a certain week or any date in the year within those that you are operational?

ri