Lookup Values from Another Table

[Guest]

I have a guest house booking database where the tariffs are based on high and
low seasons. I have a tariff field in the "rooms" table which I want to make
a list box which displays the value from the "roomtariff" field in the
"tariff" table. There are several tariffs for each room based on a start and
end date (separate fields in the tariff table) and i only want to display one
in the list box (rooms table) dependant upon the room type and the current
date matching the specific date range in the tariff table.
Any help would be greatly appreciated.

Thanks ... Dave

 

[Jeff Boyce]

Dave

A scan through this (tablesdbdesign) newsgroup will reveal a strong
consensus AGAINST using the lookup data type in a table. In fact, you'll
find a strong bias against working directly in the tables (for other than
design/development).

The most-commonly recommended approach (and I use it, too) is to use forms
to display data for add/edit/delete. In this environment, you'd simply use
a combo box (or a list box) to show the possible roomtariff values, and bind
the control to the underlying (roomtariffID-)field. Among other advantages,
working in forms gives you much more control over which records are
available, not to mention the rich event collection in forms (pretty much
absent in tables).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/