Hi,

I have an access database which I am using to list a directory of hotels and the various room prices.

The first table is a Price table. It lists all of the hotels, and the price for each season, per price category. The price category essentially refers to the various rates (for each room type). Therefore a hotel can have various price categories

Fields in Price table are:

PriceID: AutoNumber
HotelName: Text
PriceCat: Text (value is either A, B, C, D etc..)
Season1Price: Currency
Season2Price: Currency
Season3Price: Currency

Example:
1 Marriott A £xxx £xxxx £xxx
2 Marriott B £xxx £xxxx £xxx
3 Hilton A £xxx £xxxx £xxx


The second table is the RoomType table. It comprises the following fields:

RoomTypeID: AutoNumber
HotelName: Lookup (obtains values from the 'Hotel' Table)
RoomType: Text
PriceCat: Text (A, B, C, D etc... to match PriceCat in 'Price' table)

Example:

1 Marriott Business Suite A
2 Marriott Executive Suite A
3 Marriott Luxury Suite B
4 Hilton Double A


So as you can see, various room types share the same price category. However, at the moment, I am relying on people entering the correct PriceCat in the 'RoomType' table (that matches the PriceCat in the Price table). What I would really like to do is the following:

I would like to turn [RoomType].[PriceCat] into a Lookup, which lists the various PriceCat's from the 'Price' table. However, rather than list EVERY PriceCat for ALL hotels, I would like it to ONLY display the PriceCat for the relvant hotel, ie, it only lists [Price].[PriceCat] where [Price].[HotelName] matches the current record [RoomType].[HotelName].

And that's the part I can't do. When I have tried to write a query to get this to work, it prompts me to enter the [RoomType].[HotelName], whereas I would like it to get that value from the existing record.

Is there anyone who can help me or point me in the right direction as to how to achieve this?

Thanks

My Dear Friend,
Although I've found that I cannot use filter-by-form for the resulting combo boxes, the path which you seek can be found at http://office.microsoft.com/en-us/as...spx?mode=print or search Microsoft Office Assistance: "Basing one combo box on another" by Sal Ricciardi.

CG

Thanks very much CG. It looks like I have some interesting reading ahead of me!