A key(Target1Key) is formed with the combination of Field1&Field2&Field3&Field4 and is joined to Main Table
Target Table2:
Field1 Field2 Field3 Target Year
1 2 3 20$ 2015
2 3 4 30$ 2015
A key(Target2Key) is formed with the combination of Field1&Field2&Field3 and is joined to Main Table
Main Table:
Field1 Field2 Field3 Field4 Savings Field5 Field6 Year
1 2 3 4 100$ AB CC 2015
1 2 3 4 100$ BB CC 2015
1 33 44 55 200$ BB CD 2015
A key is formed with the combination of Field1&Field2&Field3(Target2Key) and Field1&Field2&Field3&Field4(Target1Key) .
When I want to show savings Vs Target for 2015
The Sum(Savings) is 400$ and Target1 is Sum(Target1) is = 300$(100 + 200) instead of 600$(100+200+300).
Field1 Field2 Field3 Field4
1 2 3 5 This combination is not present in Main Table and Since Main table is the driving table. So eliminating the 300$ because of non association.
How to over come this problem? I have to take 300$ also into consideration. I cant join or concatenate with my Main table as I have other fields in Main table(When they are selected it will be a problem). Please suggest.
