Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    show only cars available... (Access 2000)

    Hi, I am new to access. I have a problem.
    I am working on a db to keep track of vehicles hired.
    I want to show a list of only the cars that have not been booked on a certain date.

    Overview:

    For each new main date record I record the clients details & needs etc.
    In a subform within this main form I want to allow the user to pick the cars he wants to book. The details are kept in a table (carsbooked)
    I need to show a list of cars available only on that date so they can be recorded as being booked. I don't want to show any vehicles which are already booked for the date record in my dropdown menu for booking cars??
    The cars are kept in a seperate table called "cars". This is the list of the fleet of vehicles.

    If you follow me, can anyone give me some idea as to how I could acheive this.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: show only cars available... (Access 2000)

    What does a record in your CarsBooked table look like? I assume it contains at least something like CustomerID and VehicleID. But does it have one date field (a car is booked if there is a record for a certain date) or two (car is booked from Date1 through Date2)?

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: show only cars available... (Access 2000)

    Carsbooked table as follows:

    bookedref - primary
    date - date is from main form.
    carid -
    ref - individual record ref from main form

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: show only cars available... (Access 2000)

    I'm also working on something to the same effect.
    I'm re-hashing a Bodyshop management system, one of my modules is for handling courtesy cars.
    I'ts quite complex to say the least.
    Sounds to me like you need to start looking at a query system wher the car is Active/NotActive - BookedOut/NotBookedOut.
    If your project is not too big, post it to me and I'll try to help you along with it, tips advice etc.


    Regards
    Dave

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: show only cars available... (Access 2000)

    From your reply, I gather that there is an individual record in CarsBooked for each day a car is booked. In that case, I would create two queries as follows.

    First, create a query that returns the CarID of all cars that are booked for the date selected in the main form. Its SQL could look like

    SELECT DISTINCT CarID
    FROM CarsBooked
    WHERE Date = [Forms]![frmMainForm]![txtBookDate]

    Replace frmMainForm and txtBookDate by the appropriate names. I'll name this query qryCarsBooked.
    (Note: it may be too late to change now, but in general, it is not advisable to name a field "Date", because it can cause confusion with the VBA function Date. It's better to use a name like "BookDate".)

    Next, create a query that returns the CarID of all cars that have NOT been booked for the date selected in the main form. One possibility is to base this query on the Cars table and the qryCarsBooked query, with an outer join:

    SELECT Cars.CarID
    FROM Cars LEFT JOIN qryCarsBooked ON Cars.CarID = qryCarsBooked.CarID
    WHERE qryCarsBooked.CarID Is Null

    This query is not updateable. That is not a problem if you use it as row source for a list box or combo box. If you want it to be updateable, an alternative is a nested query based on the Cars table:

    SELECT CarID
    FROM tblCars
    WHERE CarID Not In (SELECT CarID FROM qryCarsBooked)

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: show only cars available... (Access 2000)

    Mik

    Just a few tips for the stumbling bricks I came across with my Fleet/Hire db.

    I tend to use batch No.s as an ID. Each time a fleet is comissioned, it will have the same batch No. This is very useful for later purposes (Vehicle Supplier) , (Batch de-Commision) , (reporting) etc etc.

    Another useful tip is put a yes/no field in the table. If a vehicle is written off or sold for some reason, you can set the vehicle Active or None Active. your display window or form can then be set to only show vehicles which are currently Active or not.
    Theirs nothing worse than over populating with vehicles you do not use.

    I've attached a pic which may give you some idea's

    Have Fun

    Dave
    If you need any help, let me know.

    Regards

    Dave
    Attached Images Attached Images

  7. #7
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: show only cars available... (Access 2000)

    Take a look at this site which offers a very elegant solution to your problem. Although it is a doctor's appointment system, it is essentially the same idea, ie, to avoid booking clashes. This solution provides a drop down list displaying only those appointments which are free.

    http://www.advanced-ict.info/databases/clash.html <font color=blue>Edited by Wendell to activate link 09-Oct-02 17:34 MDT (UTC-7)</font color=blue>

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  8. #8
    New Lounger
    Join Date
    Oct 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: show only cars available... (Access 2000)

    Thanks for the info guys. I am sorry, I haven't been around recently to respond to anyone.
    Back in country now and will get down to work on finishing this project. I will respond accordingly when I get something working.
    Hans I think your queries may be the key to my problem. I will experiment with all suggestions anyway.

    Thanks again for ideas, examples and help.

Posting Permissions

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