Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Posts
    15

    Question Unanswered: Car booking system - Help

    Hello to every one, I'm sorry if my way for asking is wrong but I am new to the forum.

    The problem I have is that I am creating a booking system for vehicles. Unfortunatly I am tied by one major problem I need to be able to book vehicles that a free between certain days and times, in other words "Blocks", What is supposed to happen is that the user will choose between what days and what times they need the vehicle and the system will show them the available vehicles that are not booked. So far I have been able to filter the dates using a select query but unfortunatly I am stuck with the time problem.

    The Bkng Table consists of :

    Vehicle ID
    Bookg ID (Auto#)
    Start Date
    End Date
    Start Time
    End Time

    Vehicle Table consist of

    Vehicle ID
    Vehicle Type

    I tried using inner join select but it dosn't Help.

    Sorry about the length of the question, but I have not been able to find an answer anywhere.

    Thanks

    Chris

  2. #2
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: Car booking system - Help

    Originally posted by ossito21
    Hello to every one, I'm sorry if my way for asking is wrong but I am new to the forum.

    The problem I have is that I am creating a booking system for vehicles. Unfortunatly I am tied by one major problem I need to be able to book vehicles that a free between certain days and times, in other words "Blocks", What is supposed to happen is that the user will choose between what days and what times they need the vehicle and the system will show them the available vehicles that are not booked. So far I have been able to filter the dates using a select query but unfortunatly I am stuck with the time problem.

    The Bkng Table consists of :

    Vehicle ID
    Bookg ID (Auto#)
    Start Date
    End Date
    Start Time
    End Time

    Vehicle Table consist of

    Vehicle ID
    Vehicle Type

    I tried using inner join select but it dosn't Help.

    Sorry about the length of the question, but I have not been able to find an answer anywhere.

    Thanks

    Chris

    I'd like to give you an answer, but it would be much easier if you attach your DB to your post, so I don't have to create an example Database myself to test it.

  3. #3
    Join Date
    Dec 2003
    Posts
    15

    Exclamation

    Thanks for your help, heres an example of what I am working on, it's a bit rough as it shows only the part I need help with. Thanks again.
    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2003
    Posts
    15
    Can Someone Please Help - I really need the advice, the DB is there for the taking as an example.

    I would really appreciate it.

    Thanks

    Chris

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    cannot read the database, but that's because i have access 97 and don't see the need to buy an upgrade

    the essence of the problem is to find vehicles which have no booking which overlaps the date range entered by the user

    assuming you had stored the bookings using datetime datatypes for [Start Date] and [End Date], so that they include both date and time components, then your query might look like:
    Code:
    select [Vehicle ID]
         , [Vehicle Type]
      from Vehicle V
     where not exists  
           ( select 1
               from Bkng 
              where [Vehicle ID] = V.[Vehicle ID] 
                and (
                    [Start Date] between [enter start date/time]
                                     and [enter end date/time] 
                 or [End Date] between [enter start date/time]
                                   and [enter end date/time]                    
                    )               
           )
    but with separate date and time components, it's way more complicated

    rudy
    http://r937.com/

  6. #6
    Join Date
    Oct 2003
    Posts
    706
    Well, what you can do in that case is to first select the records by date, then look among those records to find entries that qualify also by time.

    This is going to require the use of temporary tables, where you select the records into a table, then scan the table to delete unqualifying records. These tables are not actually "temporary" but they're used as scratch-space by your application.

    You'll be solving this problem, not with a query, but with a fair amount of Basic code.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    even if dates and times are separate fields, it would not require temp tables, just a fairly complex WHERE clause

    but the more i think about it, the more unrealistic the time part seems to me

    i've somebody has a car booked until 10 am saturday, should it show up on a search for cars starting 10:30 am saturday? no, because it takes a certain time to turn a car around -- check it, clean it, wash it if necessary, gas it...

    Chris, what's the deal with the separate time fields? can they be combined with the dates into datetime fields for start and end? and will they incorprate turnaround time so that the cars will actually be ready to go again?


    rudy

  8. #8
    Join Date
    Dec 2003
    Posts
    15

    Lightbulb

    Thanks guys for your help,

    but I have found the answer by combining the date and time fields in a separate query using the + operator and then using that query to search for vehicles, the formula is also modified to merge the Date and time Fields. It sounds complicated, but it is really simple.

    Again thanks a million for your help, and I will be browsing time and time again.

    Seeya Later

    Chris

  9. #9
    Join Date
    Oct 2003
    Posts
    706
    Dates and Times are routinely stored as floating point numbers (integer = date, fraction = time) that can be added. Note that there's usually a date-add function available that will do the same thing, "guaranteed," and you really should use that instead of relying upon an "artifact" of a particular implementation...

    But if this is a "real" application and not a textbook exercise, I think that Rudy's comment needs careful thought... it does take time to turn a car around, and people don't always turn in their cars precisely when they're supposed to. If your system isn't keenly aware of that, it can start generating bookings that don't actually work in the real world. If that happens, not only is it very likely that the agents will stop relying on the system, but customers will probably be impacted ... and you just don't get a second-chance with a customer, especially a business traveler! There are simply too many competitors in the same airport, any one of which might "have their act together" better than you do.

    Referring to another thread, this is probably the worst "system failure scenario" that can occur... the results obtained by the system don't match reality. The less costly scenario is when employees can cover for the system by simply not-using it; the disaster scene is when it makes the entire company look like a fool in front of a (soon to be "ex-") customer.
    Last edited by sundialsvcs; 12-26-03 at 12:47.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the backup, sundial, er, i mean, mister services, um...

    say, you're fairly anonymous, aren't you

    in light of your excellent commentary about getting only one chance with a customer, i just thought i'd point out that there's no name or contact info on your site, and people who might otherwise want to get in touch with you (you know, for some silly reason like perhaps wanting to hire you) have nothing to go on...

    also, i believe datetimes are always stored as two integers, not floating point -- this is true in microsoft access and sql server, but not in mysql which i believe uses (gasp!) a text string (i could be wrong though -- wouldn't be the first time)


  11. #11
    Join Date
    Dec 2003
    Posts
    15

    Smile

    Hi guys, Thanks again for your comments and I have taken in consideration the turn around, the database is real world and it dosn't get realer than the Commonwealth Goverment. What this is alll about is converting an old out of date spreadsheet system into a more efficient DB. Concerning the Turn around, as the client already have thier own rules in place, (they book the vehicle with turn around included in the time.), Hey why change thier habits if it makes creating the DB easier.

    I find the method of using the plus operand effective for my needs. I know thier may be many other ways that it can be done, but I am no Pro, because if I were I wouldn't of asked for help and again I appreciate every comment as it helps construct not only my knowladge but everyones elses who reads this thread.

    By the way heres a present for any one who desires to create a booking system.

    Tables

    Car

    Car ID
    Description

    Bkg

    CarID
    Bkg ID
    FromD
    ToD
    FromT
    ToT

    Queries

    Free

    Car ID*
    Description

    BkgQ

    CarID
    From: [FromD] + [FromT]
    To: [ToD] + [ToT]


    * Criteria is:

    Not In (SELECT DISTINCT Car.CarID
    FROM Car INNER JOIN BkgQ ON Car.CarID = BkgQ.CarID
    WHERE (((BkgQ.From)>=[forms]![form1]![From]) AND ((BkgQ.To)<=[forms]![form1]![to])) OR (((BkgQ.From)<=[forms]![form1]![From]) AND ((BkgQ.To)>=[forms]![form1]![To])) OR (((Bkg.From)>=[forms]![form1]![From] And (BkgQ.From)<[forms]![form1]![to])) OR (((BkgQ.To)>=[forms]![form1]![From] And (BkgQ.To)<[forms]![form1]![to])))


    Of Course the form itself is called Form1

    Well I hope this makes it easier to actually find a working example of Date and Time blocking. I believe in giving what I'm not going to make a profit from, and to help others. Talking about help - I have a new question. -- See it in a new post

  12. #12
    Join Date
    Feb 2009
    Posts
    1

    Thumbs up Example?

    That is excellent buddy - i am in a similar situation with pool car bookings which is currently being done on paper which is getting out of hand.

    I know a bit about databases but not enough to be successful at designing, implementing and managing one this complex.

    Could you post an example of your finished product so i can learn and tweak it to what we need?

    I would very much appreciate it.

    Many Thanks,

    Jason

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •