Count different clients through data that appears several times in different tables

[AlejandroPozo]

Hi everyone, I hope you can help me with this

I have a table that contains the information of the sellers with the products for which they are controlled

Table_control

cod_product	cod_vendor
1	405
2	405
1	123
1	234
2	678
3	678

And another table that indicates the daily sales made by sellers.

Table_daily

cod_product	cod_vendor	cod_client
1	405	12
1	123	78
3	405	23
2	405	12
2	405	32
2	123	45
1	678	67
2	678	89
3	678	67

I need to count the number of clients they visit based only on the products for which they are controlled

In this example the expected results would be the following:

cod_vendor	# clients
405	2
123	1
678	2

[parry2k · Accepted solution]

You need to add unique key column in both table which can be done using by concatenating vendor & product

so add column in both table like this:

Key = Control[cod_vendor] & Control[cod_product]

set relationship on this key betweem both tables

use table visual and drop, vendor from control table and cod_client from daily table, press arrow next to cod_client field in value deck, and choose distinct count

[parry2k]

although based on your sample data, yo will not receive any thing from 678 since it is not in control table.