Can a table be in a 1-to-many relationship with TWO other tables?

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.
Who is Participating?
SheilsConnect With a Mentor Commented:
One milestone table is definitely the way to go. The way to think about it is that the amendments are actually part of the contract. So amendment milestones are actually contract milestone. So go ahead and create you milestone table and link it to the contract table by the contractid.

A couple of things in your previous post ring alarm bells:

<MILESTONE-DATES will *not* contain the expiration date of the contract itself.>
Why not? The right thing to do is exactly the opposite. If you create a milestone table to track dates then all the dates(from start to expire) should be in it. This would make it much easier for you to generate contract progress report and query events relating to the contract.

<plus a couple of notes-type fields>
Why do you have more than one field.This tells me that you need a separate table for notes.

<Our contracts do not have many agreements>
If it has more than one condition then a separate table is justified.

At the end of the day you can create any structure but some work better than others. I don't quiet see how you can have a properly functioning database with the structure that you are talking about where there are multiple note fields in the tables and contract have multiple conditions entered as a single value. Maybe it is best to create the database just to track the contract and insert a field to hyperlink to the contract documents.This means that all you will need is two tables; tblContract & tblEvents. The event table will contain start date, end date,amendment date,milestones.... By including an event type field you can extract whichever type of event you what by query.

Short answer is yes you can.

However, I do not think that you have the correct structure.

I would have the following structure

tblContract  (holds main contract details)

tblAgreements (hold main conditions, linked to tblContract by contractid)

tblAmendments (linked to tblAgreements by the agreementid)

tblMilstone (linked to tblagreement by agreementid)
swpa_wntAuthor Commented:
Based on my prior description, what are you breaking out as "main conditions" into a separate table,  tblAgreements?
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

A contract has many agreements. These agreements can be amended/modified.

Examples of flieds that may exist in tblContracts are:

Non of the agreements should reside in the main contract table. All agreements and contract price resides in tblAgreement.

Amendment to the above agreements reside in tblAmendments

I have not made provision for new agreements. Maybe just entering it as new in tblAgrement and detailing in tblAmendment would do. Have a think about that.

The reason that you cannot put the agreement in the main contract is that each contract will have a different number of agreements which have to be separate records. You should not have a table with agreement1,agreement2,agreement3,..... or all the agreements in a single field.

I am using the term agreement for what you are calling main condition so whatever you call it same thing applies. There will be many condition to one contract. So the main conditions have a one-to-many relationship with the contract.
I am heading off to work now. To clarify me approach I have created a sample DB. It also contain a query that list the lastest conditions/amended conditions
Just just a quick amendment so that it works if you are not using long date format
swpa_wntAuthor Commented:
Hi, I appreicate your thoughts on this so far.  But perhaps I wasn't clear on the nature of our contracts. Our contracts do not have many agreements; we use the terms Contract and Agreement interchangeably, so I'll just stick with calling them Contracts.  So... we have a table CONTRACTS, which is like your tblContracts.  We also have MOD/ADD, which is like your tblAmendments.  The relationship between our MOD/ADD and CONTRACTS is like the relationship between your tblAmendments and tblAgreements.

Or, to put it another way: we have main contracts (stored in CONTRACTS), and we have contracts that modify or are addendums to the main contracts (stored in MOD/ADD ).

CONTRACTS stores customer, contract-type, execution-date, expiration-date, and other details pertinent to main contracts, plus a couple of notes-type fields for certain information.  MOD/ADD stores parent-contract-number, mod/add-number, execution-date, expiration-date (if applicable), plus a couple of notes-type fields.  If either a main CONTRACT or a MOD/ADD contract has some kind of special circumstance where only *part* of the contract expires before the rest of it, we historically have captured that information in one of the notes fields.  What I would now like to do is capture that information in its own table, called MILESTONE-DATES, which will contain: ContractNumber, MilestoneDate, and Description, where "ContractNumber" is the main contract (e.g. PG-00123) or the mod/add contract (e.g. PG-00123-M001) ; "MilestoneDate" is the date of the event we want to keep an eye on; and "Description" describes the event (e.g. Article VI expires 5 years before the rest of the contract).  MILESTONE-DATES will *not* contain the expiration date of the contract itself.

The reason for putting this information into its own table is to make it easier to query for the information (rather than trying to parse it out of all the various notes fields in the two contract tables).  However, because any main CONTRACT or MOD/ADD contract could conceivably have any number of such events, I am not certain if I should a) create *one* MILESTONE-DATES table that will store events for both main contracts and mod/add contracts; or b) simply create *two* MILESTONE-DATES tables -- one for CONTRACT and one for MOD/ADD.

My preference is to create just one MILESTONE-DATES table, but the fact that both CONTRACT and MOD/ADD will have a one-to-many relationship with it makes me think it may not be practical.  And it is this point on which I seek guidance.
swpa_wntAuthor Commented:
At this time we don't have the option of redesigning the database, so I've simply created a new table to hold milestone dates -- what I'm now calling Tickler Dates (since they are not necessarily contract "milestones" but rather are dates we want to be made aware of on our monthly tickler reports).  I've taken your suggestion to find a way to make a single table for this purpose, and realized I could do so if the table were populated directly from our contract forms.  I placed a subform, based on the TicklerDates table, on both the Contract main form and its Mod/Add subform; dates and descriptions can be entered and this data is stored in the TicklerDates table along with the contract number and mod number (if applicable).  Now such info will no longer need to be stored in the text field(s) of the forms.

The tickler report itself is based on a query that pulls from the Contracts, ModAdds, and TicklerDates tables, with special attention paid to contract expiration dates versus tickler dates, and then groups the results by year.

So I think I'm good to go for now.  Thanks, and I appreciate your thoughts on the matter.  We are aware that at some point we'll need to overhaul our database, since business requirements have changed over the years.

By the way, you asked why there are several text fields in the CONTRACT table.  Each is intended to hold different kinds of descriptive information pertinent to each contract (e.g. special start of service, unique contingencies, billing methods, etc.)  There aren't standard values for this data, so pulldowns aren't an option.
<dates and descriptions can be entered and this data is stored in the TicklerDates table along with the contract number and mod number (if applicable)>

Make sure that there is no confusion between contract number and mod number. For example you will have contract No 1 and mod No 1. You must make sure that your DB can distinguish between the two. You can do that by having fldContractNo and fldModNo in tblTickler. Then use the to link the subform to the relevant form.

swpa_wntAuthor Commented:
The tickler table does include separate fields for the base contract numbers and the mod numbers, since the database in general already distinguishes between the two.  Those fields are linked to the forms so they are automatically populated as the user enters dates and descriptions.

Well, thanks for talking this out with me.  Although I could not implement many of your suggestions, you were still helpful in getting me to think more about how I was going about this.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.