I'm Stuck - Normalization Problems

[dcrosier]

I have a database where I have been attempting to relate cases with Attorneys and Insurance Information.

Right now I have four tables:
tblCaseInfo
-txtCaseName - primary key
-txtClientAndFileInfo
-other text and memo fields

tblAttorneyInfo
-txtAssignedAttorneyName - primary key
-txtAttorneyInitials
-other text fields

tblInsuranceInfo
-txtInsCoRepName - primary key
-txtInsCoName
-other text and memo fields

tblCase
-txtCaseName - one to many relationship w/ tblCaseInfo
-txtAssignedAttorneyName - one to many relationship w/ tblAttorneyInfo
-txtInsCoRepName - one to many relationship w/ tblInsuranceInfo

I have a form based on the tblCaseInfo with subforms based on tblInsuranceInfo and tblAttorneyInfo respectively. I am relating the tables together utilizing the tblCase.

The form with their associated subforms work absolutely great! However, the results that I am getting into the tblCase is that I have a record or two with the txtCaseName and associated txtAssignedAttorneyName with blank information for txtInsCoRepName. I then have a separate record for txtCaseName, blank txtAssignedAttorneyName and appropriate information for txtInsCoRepName.

Would you have any suggestions for populating the tblCase and maintaining appropriate Normalization rules?

Thank you in advance for your valuable assistance. Tek-Tips is invaluable for someone who needs another perspective on an opportunity.

DLC

 

[nkiefer]

It seems that tblCase is not needed. I would suggest that you delete that table. Add the field txtCaseName to the attorney and insurance tables as foreign keys. You would then set a one to many relationship with tblCaseInfo and tblAttorneyInfo and tblInsuranceInfo.

 

[demoman]

tblCaseInfo
-lngCaseNumbner - Auto - primary key
-txtCaseName - primary key
-txtClientAndFileInfo
-other text and memo fields

tblAttorneyInfo
-lngAttorneyNumber - Auto - primary key
-txtAssignedAttorneyName
-txtAttorneyInitials
-other text fields

tblInsuranceInfo
-lngInsuranceCoNumber - Auto - primary key
-txtInsCoRepName
-txtInsCoName

tblCase
-lngCaseNumbner these 3 column make up the primary index
-lngAttorneyNumber
-lngInsuranceCoNumber

although it is not absolutely mandated, it is considered poor form to use names, etc. as primary keys. tblCase is required as a transaction table. otherwise, you would have many to many relationships between the other tables.

hth