Populating the "many" table in a one to many relationship

[Michaelniz]

Hello,

I have two tables that are linked in a one to many relationship:

tblContractHeader (One)
tblRatePlans (Many)

These two tables are linked via ContractID which is set to Autnumber in the tblContractHeader table and Number in the tblRatePlans.

I have a form set up to populate the tblcontractheader table and then a command button on that form that opens up a form to populate the tblRatePlans table. When I open the form and populate a record in the tblcontractheader table and then click the command button to open the form to populate tblRatePlans I get the following message when I try and populate a record in the tblRatePlans table.

"You cannot add or change a record because a related record is required in table <name>. (Error 3201)"

What I am doing wrong and how do I fix this?

Thanks

Mike

 

[boblarson]

In a one-to-many situation you normally will use a JUNCTION table to store the details.

So, you would have

tblContractHeader
tblRatePlans
tblContractsRates

and tblContractsRates would store
ContractsRatesID - PK (autonumber)
ContractHeaderID - FK (Long Integer) from tblContractHeader
RateID - FK (Long Integer) from tblRatePlans

 

[Michaelniz]

Bob,

Thanks for the quick reply. I actualy have several "Many" tables linked to tblContractHeader. These tables are:

tblVoiceRates
tblDataRates
tblFeatureRates

Can I use a single junction table for all three of these or should I use a seperate junction table for each one?

Thanks,

Mike

 

[boblarson]

It depends on what you have. I would post a screenshot of your relationships so that we can see what you currently have for fields. Or, a complete list of each field for each table.

 

[Michaelniz]

I attached an image of my table relationships. Please see the link.

Thanks,

Mike

 

[boblarson]

Woah! That definitely is going to take some redesign work. I'm not going to have time to work on that today. Hopefully we can get some other help from some others here.

 

[Michaelniz]

That bad huh? Is there any high level suggestions you could make?

Thanks again...

 

[boblarson]

If you want to really get going - try reading this to understand normalization:
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf

 

[Michaelniz]

Pat,

Thanks for the feedback. I am beginning to understand the concept you are driving at, however I am not sure it's entirely applicable to my envisioned database.

What I am looking to do is store my corporate customer's wireless contract data (Corporate contract with AT&T Mobility, Sprint, Etc.) and eventually run reports on the data.

As they are in a 1:1 relationship with the ContractHeader Table I am beging to think I can roll the fields from the Discounts, One Time Credits and One Time Charges Tables into the ContractHeaderTable.

Where I think your example/concept is applicable is linking the VoiceRatePlan, DataRatePlan and FeatureRatePlan Tables to the Contract Header table. Describing a wireless plan takes various field types from Text, Curreny, Number, Yes/No, Etc. Also note that the fields required to describe a plan change depending on the plan type (Voice, Data or Feature). This was the reasoning I used to create a seperate table for each plan type.

At typical wireless plan has many components such as gross monthly charge, Anytime Minutes Included, Overage Minute Rate, Domestic Roaming Incuded(Y/N), Etc.

The issue I am having is that in your example you simply used a single field = TranAmount to quantify the record in tblSetUpTransactions.
What table setup would you use to link a single record in the tblContractHeader to many different wireless plans?

Thanks so much for you assistance.

Mike

 

[Michaelniz]

Pat and Bob,

Over the last couple of days I've been somewhat consumed with figuring out the normalized table structure for my database. I feel that I've come a long way since I started this post and would appreciate it if you could take a quick look at my attached table relationships and let me know if I am on the right track.

At a high level I've determined that my database will track:

Companies
Contacts
Negotiation Events
Wireless Contracts
Rate Plans Associated with Contracts

Your feedback would be greatly appreciated.

Mike