Results 1 to 10 of 10
  1. #1
    joeydeck is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    26

    Check if item is available to hire

    Hello, I have been creating a Access database to hire bikes out. I have created all the tables etc but i am stuck creating a query i want to create. I want to have a sub form on my booking form from a query which will show all the available bikes that can be hired. I have looked at the lending library sample and this hasn't really helped, My database is set out differently.

    Could anyone suggest what could be done?
    I have attached a version of the database to the post, this is zipped with some screenshots which may be useful.

    Thanks for your help

    Joe
    Attached Files Attached Files

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I can't look at the attachment right now, but see if this helps with the concept:

    http://www.baldyweb.com/OverLap.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    qa12dx is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    india
    Posts
    112
    here's how i'd do it. have a look. also included some notes in the zip, cheers
    Attached Files Attached Files

  4. #4
    joeydeck is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    26

    Thankyou

    Quote Originally Posted by qa12dx View Post
    here's how i'd do it. have a look. also included some notes in the zip, cheers
    Thank you so much for your time and effort put into this. It has helped me a tremendous amount!

    Just one question, where are all the relationships? I cant seem to find them.

    Cheers
    Joe

  5. #5
    qa12dx is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    india
    Posts
    112
    glad it helped!
    forgot to mention, i deleted the relationships. i draw my own relations when i need them. because sometimes i find they don't behave well when i predefine them.

    i really dont have an opinion on it, just how i do it

    one last point, you may ve made a quick db to post and didn't change the feild size, but just in case .... access text fields are by default 255 char. always change them to a size that is more realistic. or later your db will get bloated and run slow. since its your own, it's ok to make a small field and then later increase it if u think you need more.

    cheers

  6. #6
    joeydeck is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    26

    Thanks

    Quote Originally Posted by qa12dx View Post
    glad it helped!
    forgot to mention, i deleted the relationships. i draw my own relations when i need them. because sometimes i find they don't behave well when i predefine them.

    i really dont have an opinion on it, just how i do it

    one last point, you may ve made a quick db to post and didn't change the feild size, but just in case .... access text fields are by default 255 char. always change them to a size that is more realistic. or later your db will get bloated and run slow. since its your own, it's ok to make a small field and then later increase it if u think you need more.

    cheers
    Okay thankyou, ill have ago with the db you sent. If i have anymore troubles ill post up here.


    Thanks
    Joe

  7. #7
    joeydeck is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    26
    Hello qa12dx,
    I have used your db as a template for mine. Everything works as it should but i am confused with one thing.
    Once you have made the booking where is this stored? plus how do make a new booking? If i add a new record on the booking form and select a date i know that a bike is hired out on, it shows all bikes. Is there a way that you can check if the bikes are available in the future?

    Thanks
    Joe

  8. #8
    qa12dx is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    india
    Posts
    112
    hi, the booking is stored in Booking_Tbl. also bikes hired for are stored in BikeLoan_Tbl.

    how i am seeing bike availability is when u input from and to date, i do 2 queries. once it searches the laondate fields for any matches ** to from and to dates and does a make table query* (Bikes_booked_tbl) with all the bikes booked in that date range. and then append qry to where it searches the laon return fields and appends bike ID for any hits. now i use this table to exclude those bikes form bikes available. when you hit the "check availability" button it runs these qrys and shows you available bikes

    so now if a bike falls in any part of the date range the customer wants, it will not show up.

    see the events in properties for the buttons i used. also to add new order you will have to add a button to take you to a new order record. similarly you will have to delete a incomplete order record. right now i am using the BikeLoan_Tbl - BookingID field as booking ID. so when you delete a record the booking ID's will not be sequential, if you need it so, you will have to make changes. sequential numbering isn't hard. you would use dmax function to find the last numbe and add 1 for your next number.

    *make table query because it will start new record set everytime.
    **select where loandate falls IN between from date and to date

  9. #9
    qa12dx is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    india
    Posts
    112
    Quote Originally Posted by qa12dx View Post
    am using the BikeLoan_Tbl - BookingID field as booking ID. so when you delete a record the booking ID's will not be sequential...
    forgot to mention, the ID is a autonumber field and hence the ID's wont be sequential if 1 or more is deleted.

  10. #10
    joeydeck is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    26
    Okay thankyou for all your help, at the moment im just working out how to create a report plus calculations to work out how much the hire will cost.

    Thanks
    Joe

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need to hire out relational database design
    By janakybrent in forum Access
    Replies: 5
    Last Post: 02-19-2016, 05:11 AM
  2. How to Check if a item has been hired
    By joeydeck in forum Access
    Replies: 8
    Last Post: 11-29-2012, 07:21 AM
  3. Parent Item / Child Item Not Saving Correctly Together
    By Evilferret in forum Programming
    Replies: 6
    Last Post: 08-24-2012, 02:30 PM
  4. Replies: 2
    Last Post: 05-24-2010, 06:47 PM
  5. Want to hire some to help me
    By InvGrp in forum Database Design
    Replies: 1
    Last Post: 05-02-2007, 01:46 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums