Trying to link tables (is it possible?)

[Groves22]

Hey there...

I have 4 tables and I want to link them all together for a final pivot table/chart. All of the tables have the same identifier, but a couple have an extra branch to link. I work for an insurance company so this had policy numbers and unit numbers (if the policy has more than one vehicle).

All 4 tables have the field "Policy #". 3 have "Unit #". One table is just policy level attributes so it does not include the Unit # (to avoid duplicates). Is there a way to link all these up and have the end premium in a pivot table? When I only include policy level info, no problems... it's when I join in unit level items.

I'll include quick table examples (hope it helps):

COV TABLE

Pol #	Unit #	Coverage	Limit
1	1	BI	100/300
1	1	PD	100000
1	1	COL	500
1	2	BI	100/300
1	2	PD	100000
1	2	COL	250
2	1	BI	50/100

<tbody>
</tbody>

POL ATTRIBUTES

Pol #	Pay Type	Prior BI	Multi Policy	Multi Car
1	EFT	100/300	Y	Y
2	PIF	50/100	Y	N

<tbody>
</tbody>

UNIT ATTRIBUTES

Pol #	Unit #	Model Age	Use	An Mile
1	1	5	WL	12500
1	2	13	WH	25000
2	1	2	PL	8000

<tbody>
</tbody>

PREMIUM

Policy #	Unit #	Comp 1	Comp 2	Comp 3
1	1	358	559	475
1	2	175	252	209
2	1	234	275	199

<tbody>
</tbody>

 

[cyrilbrd]

Hi,
Have you tried to create one using Powerpivot?
Tried just for fun and was able to link all your tables to your POL ATTRIBUTES table.
Maybe someone more experienced like scottsen will pitch in...
You may as well have a look at this site, might be helpful: link here.

 

[Groves22]

Yes... This was with PowerPivot. The premiums never seemed to sum or average correctly once unit level attributes were included.

 

[ImkeF]

How did you connect your tables? Created a unique identifier by concatenating Pol# and Unit#?

Problems could occur when you create a report that combines data from CovTable (n-side) and premium (1-side, lookuptable) - when you take the premium (comp 1-comp3) into the value section: This is basically the wrong way around. So if you want to analyse the premium amounts, you cannot take the elememts from CovTable in your report (think of the total sum: As the premium lines would be multiplied for each coverage type - what would the meaning of the grand total in that report actually be?)

The other lookup-table (just Pol) shouldn't be a problem here.

Imke

 

[Groves22]

I was tinkering and made a linking table... was that correct? I've attached a link to shows the sample document I am working with (maybe that will help)? I still don't know the correct way to link the tables, if at all possible.

https://docs.google.com/spreadsheets/d/15LEZ7xVejsM1gHn6N6p4wemZnofuAi_aSwODrxeXrV4/edit?usp=sharing

 

[ImkeF]

OK, might got your question now

Create a star scheme for each of your fact-tables: cov table and premium, by relating each of them to unit attributes by your key (which is fine) and to the pol attributes by pol#.

Assuming that your unit attributes has only row per item, you don't need to keep your linking table.

You need to correct your key in your Unit attr table - they are not unique (but could be - just adjust according to how you did in premiums)!

Imke

 

[Groves22]

That seems to have worked! Thank you! I think I kept linking backwards somehow...