Results 1 to 2 of 2
  1. #1
    doquan0 is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    2

    Automatic Price for Booking - Access 2007

    Hi everyone



    Please have a look at my database (in my attachment or you can download from: http://www.mediafire.com/?did7solw3wbjfwh - 54kb )

    I want to create a query that contains all information from table tbl_Booking and this query has to show the price for each BookingID
    However, this price must be dependent on TourCode and NumberofCustomer which are in table tbl_TourCode

    I'm not sure to use IIF or Dlookup function to solve this problem. Please help me
    Thanks a million.

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    this does not make much sense:

    Quote Originally Posted by doquan0 View Post
    this price must be dependent on TourCode and NumberofCustomer which are in table tbl_TourCode
    you can look up the price with dlookup, like what I've shown here, but as far as dependencies are concerned, you need to expand a little bit on what exactly the need is.

    As far as your query is concerned, this might be what you want:
    Code:
    SELECT tbl_Booking.BookingID, 
    tbl_Booking.BookingDate, 
    tbl_Booking.DepartureDate, 
    tbl_Booking.TourCode, 
    tbl_Booking.CustomerID, 
    tbl_Booking.Numberofcustomer, 
    
    DLOOKUP(IIF([Numberofcustomer] = 1, "[1customer]", 
    
    IIF([Numberofcustomer] BETWEEN 2 AND 3, "[2to3customer]", 
    
    IIF([Numberofcustomer] BETWEEN 4 AND 6, "[4to6customer]", 
    
    IIF([Numberofcustomer] BETWEEN 7 AND 10, "[7to10pcustomer]", NULL)))) , 
    
    "tbl_TourCode",  "[TourCode] = '" & [TourCode] & "'")
    
    FROM tbl_Booking;
    Also, there are numerous problems with your data:

    *No of Customers is the currency data type.
    *7 to 10 customers field is named 7to10pcustomer. TYPO.
    *The data is not normalized.

    Try some research on Access. Maybe naming conventions. It will help a lot!

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

Similar Threads

  1. Look up price
    By matt4003 in forum Queries
    Replies: 7
    Last Post: 12-28-2009, 02:19 PM
  2. Replies: 1
    Last Post: 10-06-2009, 02:00 AM
  3. booking in form
    By yoma in forum Forms
    Replies: 1
    Last Post: 07-31-2009, 05:40 PM
  4. Multiple Price Columns
    By kmwhitt in forum Access
    Replies: 0
    Last Post: 04-04-2009, 09:48 PM
  5. Make a booking availibility checker - please see pseudocode!
    By sirantonycartwright in forum Programming
    Replies: 0
    Last Post: 09-03-2007, 10:07 AM

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