Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 19th, 2005, 05:22 AM
Authorized User
Join Date: May 2005
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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:

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

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)


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?


Old May 19th, 2005, 02:58 PM
Registered User
Join Date: Apr 2005
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts

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.


Old May 20th, 2005, 04:08 AM
Authorized User
Join Date: May 2005
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup Field for Report eusanpe Access 2 March 9th, 2007 08:38 AM
Updating a Date field based on another field arholly Access VBA 6 November 22nd, 2006 11:19 AM
Calculating a field based on a lookup range nrwatson Access VBA 1 April 29th, 2004 06:41 PM
Update city field based on zip field nganb SQL Server ASP 0 April 22nd, 2004 10:30 PM
lookup table current field. Squid Access 0 December 21st, 2003 07:08 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.