Count Distinct for more than one column

[Zandra]

Hi,
I have the following fields:
Zone
Product Segment
Contracts
So, i want to count a distinct contracts per Product Segment for each Zone. In other words, i want to count distinct contracts and group them by Product Segments and by Zones. I have searched in the form and could not find any answer for my problem. I appreciate your help. I have attached small sample file for clarity reason. Thanks again.

 

[boblarson]

Is this what you were thinking of? (see query1 in attached)

 

[Zandra]

Thanks for the quick response, but this is not what i was looking for. I want to be able to count DISTINCT contracts. The query you have attached counts only number of contracts and i am looking for a DISTINCT contracts. I was researching for the last 2 days and i think it should be done in sql. I don't know if it could be done using normal queries but you are the expert. Thanks again in advance for your help.

 

[boblarson]

Actually, there's not an easy way (at least that I can see) to do that in a query. But in a REPORT now that's another matter. Very easy. See Attached (see report named rptDistinctContractsPerSegment)

 

[Zandra]

Thanks for your time. I actually don't need a report i would it to have in either query or using sql. I have a huge data and i need this to incorporate that data. I have researched and found out that it could only be done in sub query and i don't know how to do that.

 

[ByteMyzer]

Try the following SQL:

SELECT T1.Zone, T1.[Product Segment], Count(T1.Contract) AS [# of Contracts]
FROM (SELECT DISTINCT * FROM Sheet1) AS T1
GROUP BY T1.Zone, T1.[Product Segment];

 

[gemma-the-husky]

i dont see how can you have distinct contracts, and then show other attrributes FOR those distinct contracts - this just appears a meaningless concept

i can only see how you could have distinct rows of contracts/zone or contracts/productsegments

 

[boblarson]

Actually, I guess it isn't so bad. Check out Query2 which is based on the qryDistinctContractsPerSegment query in the sample.

 

[Zandra]

thank you so much boblarson, it is working now. Many thanks for your help

 

[boblarson]

thank you so much boblarson, it is working now. Many thanks for your help

Sorry it took so many iterations. I'm not thinking straight today, it would appear.