Many to Many Problem 1

[jrabenhorst]

I'm having a problem creating a many to many relationship and I was wondering if someone could shed some light on the subject. I'm working with a life insurance company trying to create a claims database that also prints checks. I have two tables created that don't like eachother. One table has policy information such asolicy Number, Insured, Amount, Claim Number etc. The other table has check information such as:check number, payee, address, claim number etc. The two common fields are claim number. But many policies can exist on one claim, and also many checks can pay 1 claim. I've researched creating a joining table? But I can't seem to make it work. Everytime I run a query pulling from the two tables, it turns up blank and I have information in the tables. My two primary keys on these tables are Check Number, and Policy Number. When I try to link by claim number, any query I run gives me repeated records. Any help with this would be great. Thanks

John

 

[lespaul]

When I try to link by claim number, any query I run gives me repeated records

Because as you said each claim may have many checks, so you get a record for each check.

What information are you trying to extract? Or are you just trying to get the relationship set up correctly?

You may want to add a third table - ClaimChecks. Remove the ClaimNumber from the CheckTable. Add to ClaimChecks ClaimNumber and CheckNumber and make them a composite primary key.

If I have completely misunderstood your problem, I apologize!!!

leslie

 

[jrabenhorst]

I'm really just trying to set up the relationship correctly. I don't have a large amount of experience with access, but I'm fairly sure I have a many to many relationship here. I want to set it up correctly, but I assume merely linking Claim number won't accomplish this, as I cannot enforce integrity.

Also, due to my limited knownledge of access, I'm not sure what you mean by "composite key". Any help would be great. Thanks

John

 

[lespaul]

in the table design when you are selecting a primary key, highlight two fields and press the 'key' button and viola, you have a composite primary key. This means that as long as the combination of the two fields is unique, the record will be added:

ClaimNumber CheckNumber
1001 2000
1001 2050
1002 1800
1003 1801
1004 1900
1004 1901

etc.

Hope that helps!

leslie

 

[jrabenhorst]

Ok, I understand how to create a composite key, but now I'm having the problem in creating my joining table. When I create it and use Check Number, and Claim #. I can't link Claim number to the policy table and force integrity because when two checks are attatched to one claim, that forces one claim number to be mentioned twice in the joining table, and of course, because many policies are linked to one claim I have the same problem in the other table. I can't seem to create the relationship I need to make this work. Any thoughts?

 

[lespaul]

Ok, now I'm confused. You should have these tables:

Policies
PolicyID (PK)
Owner

Claims
ClaimID (PK)
PolicyID (FK)

Checks
CheckNumber (PK)
Pay To
Amount

ClaimChecks
ClaimID (FK to Claims)
CheckNumber (FK to Checks)

Does this look similar to what you have?

leslie

 

[PHV]

In fact, I think you need 3 additional table:
A Claim table with ClaimNumber as PK and some fields describing the claim.
A ClaimCheck table with a composite PK on ClainNumber (FK referencing the Claim table) and CheckNumber (FK referencing the Check table)
A ClaimPolicy table with a composite PK on ClainNumber (FK referencing the Claim table) and PolicyNumber (FK referencing the Policy table)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jrabenhorst]

ok there seems to be quite a bit of confusion here. I'll detail my problem, and include the tables. I have and the reasons I have it created this way, once again, thanks for all of the help, and I'll do all I can to thank those who help.

As of right now I have three two tables created. The first is Policy infomation. It includesolicy#, Name of Insured, Policy Amount, and a Claim#. The Primary Key is the Policy# Keep in Mind Many Policies can exist on one Claim#. The other table is the Check Table. I made the Check# the primary key because I would need each check listed once so I could print checks one per page when all the information is ready. The Check table includes:Check#, Check Amount, Payee, Payee Address, and Claim Number. Once again keep in mind that Many checks can be used to pay one claim, and thus many policies. The only relationship that I can come up with is creating another table that only lists Claim# and linking it between the two tables to force integrity.

So this is my problem. I'm not sure if I've created a true many to many relationship, also I'm getting quite a few records when I try to run queries pulling fields from both tables, Essentially I get one for each check, but I'd also like to know a better way. Any help would be wonderful. Thanks

John

 

[lespaul]

I think before you go much further with this you should read up on database design and normalization. A good place to start is The Fundamentals of Relational Database Design.

The only relationship that I can come up with is creating another table that only lists Claim# and linking it between the two tables to force integrity.

That's exactly what we've been trying to explain!!

If many Policies can exist for one claim then you need:

Policies
PolicyID (PK)
Other Policy Information


Claims
ClaimID (PK)
Other Claim Information

PolicyClaims
PolicyID (PK - FK)
ClaimID (PK - FK)

If many checks can be issued for a single claim then:

Checks
CheckNumber (PK)
Other Check Information

ClaimChecks
ClaimID (PK - FK)
CheckNumber (PK - FK)



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Ronze55]

LesPaul makes a great point.

M-M relationships can not be implemented in a proper DB

you will need to create a composite entity (table) containing candidate keys from each of the two original tables.

ronze