When you choose a hotel from the drop down, should the value list enter a HotelID or the Hotel's name?
How does the Hotel table link to Names? (What fields are used as match fields?)
There are a couple of different ways that a conditional value list might be possible depending on your answers to these questions.
Selection from the Value list will put the Hotel Name in the Member's Tour record. Now you got me wondering if that makes my DB a little redundant. Perhaps it really should just have a link to the Hotel. But for now, I'll just go with copying the Hotel Name to the Member's Tour.
Just a little more info if it helps:
I must assume that __pkNameID and _pkHotelID are an auto-entered serial numbers. but given what I see, it looks like your value list should really enter the HotelID value, not the hotel name. With the two fields that can serve as a source of values from a value list, you can display a list of names but have the value list enter the associated ID number. If you can get the HotelID, showing the name from Names--if you do not have a text field in Hotel is simply a matter of the right relationship between tourer to Hotel to names.
I also don't see a table for "members" in your description so am assuming that one record in Tourer represents one member.
Since you want this on a MemberTours layout, we need to establish what relationships exist linking to that table. Does this look correct for what you have?
Tourer::_pkMemberID = MemberTours::_fkMemberID
Tours::_pkTourID = MemberTours::_fkTourID
But that doesn't clarify what field in MemberTours you would set up for selecting a hotel and how that would figure in these relationships.
Yes, all _pk's are auto enter serial #s.
Tourer::_pkTourerID = MemberTour::_fkTourerID YES, but I changed to my actual field names.
Tours::_pkTourID = MemberTour::_fkTourID YES
What does one record in MemberTour represent? Is there one record for each trip that a member takes or is there one record for each destination that is part of a tour?
MemberTour is a single tour that a Tourer went on, or is scheduled to go on. BTW, I have no problem rearranging anything to make this easier and more maintainable. Adding another table, moving fields, anything like that is possible. I'm not married to this design at all. Yes, I have the functionality I want so far, but ease of use and maintainability are foremost.
I would think that you would need a record for each "stop" on the tour though that doesn't necessarily require multiple records for that purpose in MemberTour (I'd guess that's one use for HotelDates). I'm just trying to get a sufficiently clear picture of what you have so that the needed table occurrences and relationships I might suggest that you add will work for the way that you need to for your database design.
So you want to select one additional hotel in an added field in the MemberTour record? And this hotel should be selected from the list of hotels from those that have a matching record in HotelDates and that has "Y" in the booked field?
If I have that correct, then:
Define a calculation field, constY, in Tours. Select text as the result type and enter "Y" as its calculation. This field will store a "Y" in all records of MemberTours.
Make a new Table Occurrence of Tours and HotelDates to start and link them like this:
Tours|Booked::_pkTourID = MemberTour::_fkTourID
HotelDates|Booked::_fkTourID = Tour|Booked::_pkTourID AND
HotelDates|Booked::Booked = Tour|Booked::constY
Now you can set up a value list using the specify values from a field option to list _fkHotelID from HotelDates|Booked and you can select "include only related values starting from MemberTours" to get a value list of only Booked hotel ID's.
But this leaves you without a hotel name to list in the value list so that you can select hotels by name. You can link in an additional table occurrence of Hotel and then (if there is no text field in Hotel that stores the hotel name) an occurrence of Names linked to Hotel. This can allow you to select the Name field from one of these new occurrences as the "second field" in your value list.
Thanks Phil, it's all working now!!!