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.ParentContractNumb
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.
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.ParentContractNumb
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.
ASKER
Based on my prior description, what are you breaking out as "main conditions" into a separate table, tblAgreements?
A contract has many agreements. These agreements can be amended/modified.
Examples of flieds that may exist in tblContracts are:
id,startdate,client,name,d
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,agre
Examples of flieds that may exist in tblContracts are:
id,startdate,client,name,d
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,agre
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
contract.accdb
contract.accdb
Just just a quick amendment so that it works if you are not using long date format
contract.accdb
contract.accdb
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
ASKER
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.
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.
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)