Grouping

[Mark]

Hello all,

I hope I can explain this. Le me first show you the data:

PP Nest
857 000123
857 000123
857 000123
857 526987
857 001450
857 001450
890 479365
890 679909
760 678900

I need a query that groups on PP and counts how many there are, but only
count it once if Nest is duplicated. So in the data above PP 857 would be
counted as 3.

 

[Michel Walsh]

SELECT pp, COUNT(*)
FROM (SELECT DISTINCT pp, nest FROM tableName) AS x
GROUP BY pp



The inner most query remove dups, the outer one make a standard grouping.



Vanderghast, Access MVP