nothing

So you want a calendar of every available day for each "attraction"...

Dependant on how you store your date this can be simple or tricky. I personally would use some sort of cross-join query to create the available days (ie: the days they are NOT booked out) and then make these days available in a combo box or similar. It could take a while to explain exactly what I mean, though if you understand the principle, this should give you enough to get going on it.

Maybe you could give us an insite to your data structure... how are you storing the data at the moment?

The main table reservations:

reservation_nr
attraction_id primary key
customer_id
date_start_hiring primary key
date_end_hiring primary key


The customer_id get's it's information from the customer table and attraction_id get his information from the attraction table.

Is this enough?

Just my concept and dont know if there is a much better way of doing it, but here goes (people feel free to let me know a better way to do this!)... hmm, this is gonna take a little while, I will post the complete "how to" after I have had my lunch... there is quite a process to it and I expect it is using techniques which are slightly dubious, but it works whenever I have had to do this kind of thing... and also it makes useful use of the cross-join which is why I like it (as well as one cunning bonus feature, boy I know how to build something up!)... also I made it up, all myself (it kinda shows too!)... see you after lunch...

i am so exiting i can't wait

Right, hopefully this will take you far enough to understand what is happening and how it may be of use to you... once done, it really isnt complex, just needs a little explaining, since it is the data that is doing all the work here...

k, so...

1. Build a table called "tblDay", it should have 2 fields (DayID (autonumber) and Day (Text(8) is fine) The Day field can be populated with whatever you want but the DayID should be the numbers from 1 to 31, including 31. In my example I use Day = 1st, 2nd, 3rd etc.

2. Build a table called "tblMonth", it should have 2 fields (MonthID (autonumber) and Month (Text(12) is fine). The Month field should be the months in text... but can be anything... we won't actually use it! But again the ID should be the numbers from 1 to 12, including 12.

3. Build a table (can you guess?), called "tblYear", it should have 2 fields (YearID (autonumber) and Year (text(4) is fine)... unlike the previous two tables, this Year field will be used... but not the ID... so you need to put in the Years you think you are going to use... you could do 1900 to 2100... should cover everything!

4 Recap... you now have three tables (Day, Month and Year) which hold the numbers 1 to 31, 1 to 12 and Years X to Y (you decide!)... rather cunning with a cross-join query and a date validator this can now provide you with a list of all the days from 1-1-X to 31-12-Y, and any days which are not validated as actual dates will be ignored... the SQL for this is:

SELECT tblDay.DayID
     , tblMonth.monthid
     , tblYear.Year, Format([dayid] & "/" & [monthid] & "/" & [year],"yyyymmdd") AS USDataFormat
     , Format([dayid] & "/" & [monthid] & "/" & [year],"dd\/mm\/yyyy") AS UKDateFormat
FROM tblDay
     , tblMonth
     , tblYear
ORDER BY Format([dayid] & "/" & [monthid] & "/" & [year],"yyyymmdd");

If you put this into a query, you should see a list of all the dates from 1-1-X to 31-12-Y, and check the leap-years to confirm no "extra" dates have found their way in!

We can now use this query as the base for our date ranges.

5. Here is the bonus... I assume there are days when your business is closed... now by creating a table with a list of these dates, you can now exclude these dates from the available dates quite easily. Start a new query and add the base date query (above) and the list of dates you wish to exclude, dont join them at all... simply add the Date from the base date query and then in the Criteria for this field reference the date field you want to exclude... sample code, using a list of excluded dates (tblExDate - field: Date), and by base date query is called qryDateFormats...

SELECT DateSerial([year],[monthid],[dayid])
     , qryDateFormats.USDataFormat
FROM qryDateFormats
   , tblExDate
WHERE (((DateSerial([year],[monthid],[dayid]))<>[date]))
ORDER BY qryDateFormats.USDataFormat;

So, if I enter 25-12-2007 in the tblExDate date field, then it will not show up in the result set of this query.

-----------------------------------
Assuming you are happy and understand the process thus far, you should now understand that you can introduce other date fields using this base query and get the available days returned... you can use this for many different thing... working day calculations is a good one... between two dates... probably worthwhile having a play with it to see what happens, but without getting too close to your data, you will need a >[Start Date] and <[End Date] type of thing.

Good luck, and hope it helps.

Lee

You gotta watch out on the formatting for the date sql bits... I am working on a dutch keyboard setup and sometimes things come out odd...

"dd\/mm\/yyyy"

should read

"dd/mm/yyyy"

Dunno why it is doing that?!

that's doesn't matter to me, i work a dutch keybaord too, i am dutch;)
I am trying out your sistem, i will post when i am ready.
Thank you

hopefully you will understand the principle involved (the whole thing, not just the date format!) and be able to make it work for your data...

ok i am ready with testing it,
It does works fine and i do understand it, but that not what i needed.
Look,
i am making a system that is making reservations of attractions that can be hired. The problem is that i can make double reservations.
For example: you hired a car from 10-10-2006 till 20-10-2006 and the next day i want to hired that same car from 11-10-2006 til 18-10-2006. So my system say OK. But it's not ok because it is already hired for the uniqe combination "car001 - 10-10-2006 - 20-10-2006"
So the big problem is that i have "redendation"in my database.
I realy thank you for your attemt to solve my problem!
My question to you is: can you please try to do it again.


Thank you very much

(my englisch sucks a little but i hope you understan everything ;)