Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    11

    Unanswered: Conditional query?

    Making a dbase to book cottages as mentioned in previous post.

    I have a booking form
    (fields:Booking ref(autonumber),
    Booking Date (date/time)
    CustomerID(text)
    CottageID(text),
    BreakID (lookup W=week,WE=weekend,MW= mid week)
    Datecommencing (text masked for long date)
    Week No. (updated automatically from datecommencing)

    I need to use it to check availability. So I need a query which checks the weekNo, cottageID, and break ID (if this = W, then WE & MW are not available, if this = WE or MW then W is not available, but you can have MW and WE on the same week number).

    Not a clue how to do it. Know how to query by form, but not how to add conditions.
    Last edited by pabtyf; 12-16-03 at 08:39.

  2. #2
    Join Date
    Nov 2003
    Posts
    34
    Just a couple of questions...

    If this is truely a "booking form" that a receptionist would use why do you have key fields showing instead of the actual data? For example: Customer ID is on the form - shouldn't it be customer name, address, phone, ect.?

    Is it necessary to specify break ID and if you do need it (which I could only see a use for pricing information) why don't you just put it in the code so the user doesn't have to deal with it.

    I would suggest drawing out your screens, then making your tables. I would probibly have:
    Customer Information: such as name, address, etc
    Call Information: Date/Time of Reservation, Date/Time Called
    Reservation Information: Date arrival, date departure, room
    Pricing information: Deposit left, total price, total due
    Room Information: Num of Beds, Smoking/Non, Floor, Jacuzzi?,etc.

    Then you could include a tab or query button that pulls up a form for checking availablity by putting in arrival time, departure time, room type, etc.

    If I were the user and wanted to query to see if a room was available, I would enter in (minimum) the arrival and departure date...then I would want to furthur narrow down my search by bed type (king, two doubles, one double,etc) and then pick smoking or non...I would also want to be able to look under Jacuzzi suite or kitchenette and certain items like that.

    Just a suggestion. Let me know if I can be of any help.
    "If you are good you will be assigned all the work. If you are really good you will get out of it."

  3. #3
    Join Date
    Dec 2003
    Posts
    11
    I have drawn up my tables and got

    Customer: Id Name Address Telephone Email
    Cottage: Id, name address, owner ID, and the specs e.g no. of beds, sea view etc.
    Booking: bookingref, booking date, breakID, etc as listed.

    The break ID refers to Week (sunday thru saturday)
    Week end (friday thru sunday)
    Mid week (monday thru thursday)

    so if the customer wants a weekend break in say cottageID 2 they can't if a week has already been booked. thats why I need the break ID.

    In theory when the customer calls. the user will enter their details in customer form, the move on to cottage form which (generates a query matching their criteria), and finally on to the booking form. Entering the customer ID and the cottageID they just generated (these two field will probably be filled in automatically,if I can figure out how to do it!)

  4. #4
    Join Date
    Nov 2003
    Posts
    34
    I still don't understand what you mean by "If a customer wants a weekend break in say cottageID 2 they can't if a week has already been booked. thats why I need the break ID"

    As far as the forms...you don't even need to display the customer id...leave it in the backend. I would have tabs for each form (one for customer, one for availability, one for booking) so you can go back and forth easily) Also, you should be able to book right off the availability query so the user doesn't have to reenter all the info.

    If I were you I would go back to the drawing board with your screen designs (it doesn't sound like you've finished that part for sure yet), then design the tables, then worry about the queries. if you need help with tables let me know.
    "If you are good you will be assigned all the work. If you are really good you will get out of it."

Posting Permissions

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