The image below is a simplified version of my structure.
So I have my main data set (Encounter_FACT) which I want to link to two seperate benchmark comparitors each with different fields and structures.
So to link between ENCOUNTER_FACT and NATIONAL_COMPARISON I link on Prod, type and group_nat
to link between ECOUNTER_fACT and BENCHMARK_COMPARISON i link on Prod, type and group_bench.
This pulls the data in fine and I can use set analysis to calculate the different benchmark comparisons, however
the BENCH_LOCATION & NAT_LOCATION in the seperate tables are actually related and I want to be able link these so i can use in list boxes etc.
I was looking at creating a link table or combined table of the BENCHMARK and NATIONAL but the problem is the link back to the ENCOUNTER table uses different versions of group and so it is either one or the other and these "groups" are not a 1:1 relationship to allow me to link the potentially combined table 1:1
any help with the structure would be helpful