?
Solved

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

Posted on 2011-05-09
11
Medium Priority
?
263 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:swpa_wnt
  • 7
  • 4
11 Comments
 
LVL 16

Expert Comment

by:Sheils
ID: 35723267
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)
0
 

Author Comment

by:swpa_wnt
ID: 35723359
Based on my prior description, what are you breaking out as "main conditions" into a separate table,  tblAgreements?
0
 
LVL 16

Expert Comment

by:Sheils
ID: 35723872
A contract has many agreements. These agreements can be amended/modified.

Examples of flieds that may exist in tblContracts are:
id,startdate,client,name,description,type,comments

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.

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 16

Expert Comment

by:Sheils
ID: 35723895
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.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 35724555
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
contract.accdb
0
 
LVL 16

Expert Comment

by:Sheils
ID: 35724677
Just just a quick amendment so that it works if you are not using long date format
contract.accdb
0
 

Author Comment

by:swpa_wnt
ID: 35729399
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.
0
 
LVL 16

Accepted Solution

by:
Sheils earned 1000 total points
ID: 35730869
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.

0
 

Author Comment

by:swpa_wnt
ID: 35749105
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.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 35750691
<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.

0
 

Author Comment

by:swpa_wnt
ID: 35755505
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.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question