Issue with relationship for Group by and tables

[Anonymous]

Hi Team,

I need some guaidance on how to best approach this. We have three tables. 

Table 1:  all the data of different devices group by month. Same devices different results for each month. 

Year	Month	value	device	Site
2019	11	1_green	WRX	AAL
2019	11	0_red	WRX2	AAL
2019	12	0_red	WRX	AAL

Table 2: taking a source of device table, grouping the data by month for sites. Each site can have mulitple devices. value is green because we are taking max number of that of devices.

Year	Month	Site	value
2019	11	AAL	1_green
2019	12	AAL	0_red

Table 3: It is another table which all the devices listed by site.. Intent is for this table to get unique value of device.

Table 1 and 2  expected to have duplicate values because data is being group by month of the same site and devices

I need help creating realtionship between Table 2 and 3. Currently, I have many to one relationship from Table 3 to Table 2. But I got error Table 2 contains duplicate value and is not allowed on one side of many to one relationship or columns that are used as primary key of a table. I'm using site as key to connect them.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

For the error message above, there may be  duplicate values in the Table2, so it is unavailable to keep Table 2 on one side of many to one relationship. You might use Many to Many relationship or change the table direction of many to one relationship for Table 2.

Best Regards,

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Does anybody have any guidance on this?