I'm not quite sure what you are asking, but this may give you some ideas.
If I understand correctly, Supplier 1 may have three Tours (Tour A, Tour B and Tour C). However, each tour can be offered more than once. Therefore, you would have a one to many relationship between Tours and Tour Dates. Therefore, there would be an additional table that connects Tours to Tour Dates.
Based upon a Tour, you can set up a pop-up (or drop-down) based upon the values in the Tour Dates table. If the Tour has a set price, then that price would reside in the Tours table. However, if the Tour price changes (based upon seasons, weekends, etc.), then the price would reside in the Tour Dates table.
So, in your Join table, you can select a Supplier, a drop down list could list the possible tours for that Supplier, and then another drop down list would allow you to select the Dates.
Is this what you are trying to accomplish?
No. On my suppliers table I have a portal which allows me to display all the tours this supplier offers - Keep in mind this tour can be operated by different suppliers so the portal is a joint table where Suppliers and Tours are matched up.
So now if I go to Supplier A layout
A portal displays that supplier A Offers
And I can allocate Tour 4 if I wanted to so on...
For each supplier I have a rate year meaning a validity of rates for the season so I have a table called rate years which contain two fields start date and end date this rate year is assigned to each supplier - On the supplier table I have a portal showing the rate years lets say
Jan 1 2009 Dec 31 2009
Jan 1 2010 Dec 31 2010
So far I have the following Supplier-------<JointSupplierTour>----------Tours
Then Suppliers--------<Rate Years
Now I have built up a pricing table in here I select a record from teh Jointsuppliertour table and I say that the price is $20 for the rate year of
Jan 1 09 to Dec 31 09 - In this table I am obviously selecting the record from the Jointsuppliertourtable, I am also selecting the rate year and I am entering the pricing.
So now I know that Tour 1 operated by Supplier A costs me $20 valid this year
Rather than going through this pricing table and entering one record at a time. I have a portal as mentioned above where I display the tours that have been allocated to Supplier A - The idea is to also be able to enter the pricing on a portal from the Supplier Table and enter the validity rate year for all records on the portal at once.
Something like this
I am on the supplier A Layout - I go to a pricing tab - I select the rate year I am looking for from a drop down - I select Jan 1 to Dec 31 09 and below displays a portal with all the tours assigned to this supplier
All three tours need pricing so I enter them $20, 40 and 60 respectively but the field for the validity rate is already pre-filled as I already have selected the rate year above.
I hope is clearer now.
I want the tour date to reside in its own table and be assigned at the supplier level rather than the tour level - All tours operated by the same supplier have the same validity dates
Thank you for the clarification.
You are still going to have to enter the rates for the tours. If each supplier has different rates for tours, then they will have to be entered in a Join table. That is, Tours to Suppliers.
Otherwise, you can keep the price in the Tours table. However, it looks like you have the Rate Years applied to the Supplier. That means, all Tours are priced the same, which is contrary to what you told me in the first post. You can set up a script to create records for the next year's pricing for a Supplier for each tour. Your join table would then include date range which you can use as part of the relationship when selecting tour dates.
I have been looking at this for hours now - I have reworked the database so that I enter pricing on the join table being the Tours to Suppliers.
You are correct The rate years I have applied them to the supplier - Now my question is how do I allocate several rate years to the same record that is located on the joint table
My joint table display a an supplier operates Tour A - How do I enter pricing here based on a rate year?
How many table occurrences do you have of Suppliers? Do you have one just based on Supplier ID? Or, do you have it based on Supplier ID and Date? If one, then you maybe want to have one for each. That way, with the second table occurrence based on Supplier ID and Date, you will be able to find just those records for the date range where you can enter the rates for one particular year. Otherwise, you can show all tours for each year and enter the rates for each year.