By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,416 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

Count nr of unique group of records

P: n/a
I have a table with 5 fields. In a query I would like to make a count for
every unique combination of field 2,3, and 4. I fiddled with the count and
dcount option, but can't get it to work for more than one field. Next
question is if it's possible in such a query to show only those unique
combinations that have a count higher than 5.
Thanks,
john
Sep 4 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

SELECT DISTINCT F2, F3, F4
FROM Table6

Will give you the unique combinations of the three fields.

And
SELECT DISTINCT F2, F3, F4
FROM Table6
GROUP BY F2, F3, F4
HAVING Count(*)>5;

Will give you the unique combinations of 3 fields where there are more than
5 records with that combination.
--

Terry Kreft
"john" <jo**@test.comwrote in message
news:Dt********************@casema.nl...
I have a table with 5 fields. In a query I would like to make a count for
every unique combination of field 2,3, and 4. I fiddled with the count and
dcount option, but can't get it to work for more than one field. Next
question is if it's possible in such a query to show only those unique
combinations that have a count higher than 5.
Thanks,
john


Sep 4 '06 #2

P: n/a
Thanks. That did it!
john

"Terry Kreft" <te*********@mps.co.ukschreef in bericht
news:j8******************************@eclipse.net. uk...
>
SELECT DISTINCT F2, F3, F4
FROM Table6

Will give you the unique combinations of the three fields.

And
SELECT DISTINCT F2, F3, F4
FROM Table6
GROUP BY F2, F3, F4
HAVING Count(*)>5;

Will give you the unique combinations of 3 fields where there are more
than
5 records with that combination.
--

Terry Kreft
"john" <jo**@test.comwrote in message
news:Dt********************@casema.nl...
>I have a table with 5 fields. In a query I would like to make a count for
every unique combination of field 2,3, and 4. I fiddled with the count
and
dcount option, but can't get it to work for more than one field. Next
question is if it's possible in such a query to show only those unique
combinations that have a count higher than 5.
Thanks,
john



Sep 4 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.