table relationship

[12Bowers12]

Hello, everyone,

I have been stuck a question for a long time.

For auto insurance policy, one policy comes some coverage, such as BI, PD and PIP.

Given one policy, there can be more than one accident. For each accident, more than one coverage might be involved.

For example, in the attached four tables, Policy table and PolicyData table are 1-M relationship. Policy table and Claim table are also 1-M relationship. Claim table and ClaimData are also 1-M relationship.

Question 1: how to build relationship among the tables

Policy and PolicyData is straight, 1-M.

But for Claim table and ClaimData table,  can be Policy to Claim to ClaimData as one 1-M chain, also can be Policy-Claim (1-M), and Policy-ClaimData(1-M).

Which approach is optimal?

Question 2: how to combine all the information together, in a new Calculated table or Report?

The New Table (or Report?) should include: Policy Number, Effective Date, Coverage, Limit, Claim Number, Loss Date, Amount.

I preferred a Report solution.

I would like to thank you first.

Dennis

Policy				PolicyData
Policy Number	Effective Date			Policy Number	Coverage	Limit
456	1/2/2014			456	BI	20
321	3/5/2014			456	PD	25
				456	PIP	60
				321	BI	30
				321	PD	65
Claim				ClaimData
Policy Number	Claim  Number	Loss Date		Claim  Number	Coverage	Amount
456	101	2/2/2014		101	BI	55
321	205	4/8/2014		101	PIP	63
456	506	4/6/2014		205	PD	44
				205	BI	78
				506	PD	55

[Ashish_Mathur]

Hi,

I think the relationships should flow like:

1. Policy Number of PolicyData Table to the Policy Number of Policy Table
2. Policy Number of Claim Table to the Policy Number of Policy Table
3. Claim Number of ClaimData Table to the Claim Number of the Claim Table
4. There should be a 5th Table with a single column of all unique Coverage entries.
   1. There should be a relationship from the Coverage Column of the PolicyData Table and ClaimData Table to the 5th Table

Hope this helps.

[12Bowers12]

Thank you, Ashish,

Can I send this simple Power BI data to you for a test?

I can be reached at dli@cwico.com.

Sincerely,

Dennis

[Ashish_Mathur]

Hi,

You are welcome.  Have you tried my solution?  Has it worked?