How to create SQL-Statement for this

[BigSebek]

Hello, I have a problem to build a SQL for this tables created with Access:

Table1:
No|ProductID|Count
1|10|4
2|10|4
3|10|4
4|11|24

Table2:
No|ClientNo
1|100
2|100
3|101

I have to know how much Products with the No. 10 have been bought from different clients.

The solution should look like this:
ProductID|ProductNo|DiffClients
10|12|2

I am trying since days and I don't know how to find a solution.
I have tried s.th. like this:

select Table1.ProductID,sum(Table1.count),count(Table2.ClientNo) from Table1,Table2 where Table1.No = Table2.No group by Table1.ProductID

With this statement I always get the DiffClients with the value 3, this i wrong in this case I have to get the value 2. When I add to group by: table2.ClientNo I get two rows with the same ProductID, this is also wrong I will get one row.

Can someone help me please?

[erickwidya]

first..i want to ask something
1. ProductID|ProductNo|DiffClients --> ProductNo comes from where???
2 .

... have to know how much Products with the No. 10 have been bought from different clients.

when u said No..is it refer to ProductNo or just NO.???
3. i believe that ur No at Table1 is related to No at Table2, is this correct?
4. at table1..why u have different No for same ProductID?

[BigSebek]

Hi, the both tables are related with the field No. What I mean by Products with the same No ist ProductID. I want to know how many products with the same ProductID f.e. 10 has beand tatolly bean bought by how many different Clients. The ProductID comes form somewhere else. It doesn't matter here. The ralation between the two tables is given by the field No. This ist something like the InvoiceNo. I hope you can help me.

[erickwidya]

Table1:
No|ProductID|Count
1|10|4
2|10|4
3|10|4
4|11|24

Table2:
No|ClientNo
1|100
2|100
3|101

isn't that the result would be
ProductID|ProductNo|DiffClients
10|12|3
coz..the part in red is with same ProductID..

if it is then here is the syntax

SELECT [Table1].[ProductID], Sum([Table1].[Count]) AS TotalCount, Count([Table2].[ClientNO]) AS DiffClients  
FROM Table1 INNER JOIN Table2 ON [Table1].[No]=[Table2].[No]  
GROUP BY [Table1].[ProductID];

hope this is what u want..

regards

[BigSebek]

Hi, first thanks for your help. Your statement is not 100% correct. This result has 3 DiffClients. But it must be 2 DiffClients (100,101) The Client with the No 100 is twice in the Table, so you have to count him once. This is the problem which I have. How to make visibly two DiffClients without distinct rows.

[erickwidya]

well..i can manage to do with 2 query..don't if this what u want or not..(in Access)
well this is it

queDistinct

SELECT DISTINCT Table1.ProductID, Sum(Table1.Count) AS SumOfCount, Table2.ClientNO
FROM Table1 INNER JOIN Table2 ON Table1.[No] = Table2.[No]
GROUP BY Table1.ProductID, Table2.ClientNO;

queFinal

SELECT queDISTINCT.ProductID, Sum(queDISTINCT.SumOfCount) AS TotalCountt, Count(queDISTINCT.ClientNO) AS DiffClients
FROM queDISTINCT
GROUP BY queDISTINCT.ProductID;

PS : this is the query from ACCESS