Create relationship with duplicate foreign key

[brian0782]

Hi everyone

I'm new to Powerpivot and data modelling (somewhat). I have a fairly easy relationship I'm trying to create but getting stuck. I'm sure the answer is easy!

What I'd like to do is link these two tables by Transaction ID but getting a duplicate key error on the second table. I want to summarize the transaction amounts (row label) by line items paid (column label).

Again, I'm sure this is quite obvious but what am I doing wrong?

Thanks in advance, Brian

[TABLE="width: 500"]
<tbody>[TR]
[TD]Transaction Id[/TD]
[TD]Transaction Total[/TD]
[TD]Transaction Date[/TD]
[/TR]
[TR]
[TD][TABLE="width: 266"]
<tbody>[TR]
[TD="width: 266"]OLP109388[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]63[/TD]
[TD]19/07/2014[/TD]
[/TR]
[TR]
[TD][TABLE="width: 266"]
<tbody>[TR]
[TD="width: 266"]OLP109386[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]63[/TD]
[TD]19/07/2014[/TD]
[/TR]
[TR]
[TD][TABLE="width: 266"]
<tbody>[TR]
[TD="width: 266"]OLP109394[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]65.85[/TD]
[TD]19/07/2014[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Transaction Item Id[/TD]
[TD]Transaction Id[/TD]
[TD]Transaction Item Total[/TD]
[TD]Transaction Item Amount[/TD]
[TD]Product Code[/TD]
[TD]Transaction Item Date[/TD]
[/TR]
[TR]
[TD]1000001[/TD]
[TD]OLP109388[/TD]
[TD]63[/TD]
[TD]63[/TD]
[TD]301010[/TD]
[TD]19/07/2014[/TD]
[/TR]
[TR]
[TD]1000002[/TD]
[TD]OLP109386[/TD]
[TD]63[/TD]
[TD]63[/TD]
[TD]301020[/TD]
[TD]19/07/2014[/TD]
[/TR]
[TR]
[TD]1000003[/TD]
[TD]OLP109394[/TD]
[TD]63[/TD]
[TD]63[/TD]
[TD]301020[/TD]
[TD]19/07/2014[/TD]
[/TR]
[TR]
[TD]1000004[/TD]
[TD]OLP109394[/TD]
[TD]2.85[/TD]
[TD]2.85[/TD]
[TD]101010[/TD]
[TD]19/07/2014[/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

At least one of the tables must have only unique values in the table, on the field you are linking by relationship. It appears you have duplicates... you need to clean that up.