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

 

[Pat Hartman]

Start with Discounting, OneTimeCredits, and OneTimeCharges. These should all be a single table with the following fields:

tblSetUpTransactions:
SetUpTransactionID (autonumber primary key)
ContractID (foreign key to contract header)
TranTypeID(foreign key to tblTranType)
CreditInd (long) (Credit = -1 and Debit = 1 so you can multiply amount by this field to get the actual value)
TranAmount (currency - absolute value)


tblTranGroup:
TranGroupID (autonumber, PK)
TranGroupDesc (discounting, one time credits, or one time charges)

tblTranType:
TranTypeID (autonumber, pk)
TranGroupID (fk to tblTranGroup)
TranTypeDesc (Equipment, EFT proration, Loyality Credit Line Level, etc.)
InactiveCD (Y/N - default to false - used to inactivate an individual debit/credit)

This structure allows you to have an infinite number of groups and items within the groups so as the business changes over time, the table schema will not need to change. For example, if the company wants to add a special discount that is active for only a short while, they can just add it to TranType. This is the power of a properly structured relational database.

You need to look at all the other tables to see if you have the same situation where you have multiple instances of a particular type of data. The features table is another candidate. Normalizing that table will allow the addition of new features WITHOUT ANY PROGRAMMING CHANGE - that's your manager smilling as he hands you your bonus check.

And finally - change the column names of the remaining columns to remove special characters and embedded spaces. CamelCase is the prefered name formation style these days or you can use underscores as in camel_case - with and without caps.

 

[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

 

[Pat Hartman]

The link goes the other way - from the child to the parent. So ContractID belongs in the wireless plan table.

Check with ATT. They may be able to send you the bill in a machine readable format. Then use that to help define your tables. Their format may be XML which is the most flexible interchange format. You can import XML into Access but you may need a schema file to do it automatically.

My design will make analysis easier believe it or not. With the flattened schema, you have to perform calcualtions on each specific column. With a normalized schema, you have to perform calcualtions on only a SINGLE column. You then transform the results with a crosstab if you want something that looks like a spreadsheet.

 

[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