Automatic Price for Booking - Access 2007

[doquan0]

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.

[ajetrumpet]

this does not make much sense:

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:

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!