How can I set up a table that has a one-to-may relationship with two other tables, without making it some kind of "junction" table between the two?
A little background:
In Access 2007, we have a table that stores data on main contracts ([CONTRACTS] and one that stores data on agreements that modify or amend main contracts ([MOD/ADDS]). The two tables are related on CONTRACT.ContractNumber = MOD/ADD.ParentContractNumber.
Any Contract or Mod/Add that we create can have one or more "milestone dates" in it -- for example, even though the Contract (or Mod/Add ) runs until, say, July 1, 2020, Article II and Article V might expire in 2015, or maybe a certain portion of the agreed-upon work needs to be completed by September 30, 2012.
Previously we captured this kind of data in various text fields of the CONTRACT or MOD/ADD tables. Now, however, I'm creating a "tickler" report that is intended to give a heads-up on upcoming milestone dates. So, rather than try to parse date strings and such out of the various text fields with a query, we are going to create a new table to hold the important dates and their descriptions. This new MILESTONE_DATES table will be in a one-to-may relationship with both the CONTRACT table and the MOD/ADD table, and will contain only the following fields: Index, ContractNumber, MilestoneDate, and Description.
So here's my question again: How can I set up a table that has a one-to-may relationship with two other tables, without making it some kind of "junction" table between the two?
Thanks for any help.