Create lookup based on field value
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:
PriceCat: Text (value is either A, B, C, D etc..)
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:
HotelName: Lookup (obtains values from the 'Hotel' Table)
PriceCat: Text (A, B, C, D etc... to match PriceCat in 'Price' table)
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?