Relationship questions

  • Thread starter Thread starter azu_daioh
  • Start date Start date
A

azu_daioh

Can someone help me understand how to establish proper relationships
between these tables.

[tbl_Claimant]
SSN (PK), FirstName, LastName, DOB

[tbl_ClaimantAddress]
clmtAddrID (PK), SSN (FK), cAddress, cCity, cState, cZipCode

[tbl_ClaimDetails]
clmID (PK), SSN (FK), CED, FO, Diagnosis, ProgDate, DurcDate, etc

They are actually from 1 worksheet and I tried to do normalization and
came up with 3 tables.
But, I'm having trouble creating the relationships or rather unsure if
I did it right.

Relationship:
[tbl_Claimant] 1-Many [tbl_ClaimantAddress] Enforce Referential/
Cascase Update, Join type#1
[tbl_Claimant] 1-Many [tbl_ClaimDetails] Enforce Referential/Cascase
Update, Join type#1

Scenario
Each claimant may have multiple claims and each claim may have
multiple addresses for claimant throughout the claim. So...should I
have a direct relationship between [tbl_ClaimDetails] and
[tbl_ClaimantAddress] instead?

Greatly appreciated if someone can enlighten me.

Thank you,

Sharon
 
It sounds like you are saying that the Address depends on the Details, not
on the Claimant. I'm thinking of direct relationships.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I would add a field named Current or Active in [tbl_ClaimantAddress].

I also would add a table [tbl_Claims] between [tbl_Claimant] and
[tbl_ClaimDetails]. For each claim you probably will need multiple records
for the details.
 
Back
Top