asked on

Having difficulties to create a query to count distint conference per user

I'm having difficulties to create this query (in msaccess).

Let's say i have this table:

USER CONF PART
1 1234 Bob
1 1234 Janet
1 1234 Patrick

1 1235 Bob
1 1235 Alicya

1 1236 Bob
1 1236 Janet

2 1237 Bob
2 1237 Patrick
2 1237 Janet

I would like to count the number of CONF per USER, to get something like this:

CODE NCONF
1 3
2 1

Yet i've tried something like this, but i'm getting the number of participants per conference, not the number of distinct conference per user.

SELECT USER, CONF, COUNT(*)
FROM MyTable
WHERE <some date range>
GROUP BY USER, CONF

Open in new window

Anyone can help me?

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Olaf Doschke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Christian de Bellefeuille

ASKER

@_agx_: Perfect. You were right, the first method didn't worked because a DISTINCT within a COUNT is not supported. But your 2nd method work great with the subquery

@Olaf: You are right. When i've created the example, i've done it from my tables where column names would not make sense much except for me. So i've renamed it for the "example"... so instead of Code in the 2nd table header, it should be USER.

Olaf Doschke

OK, that lifts some confusion, now I'm still a bit puzzled why PART isn't user, but I conclude it's not important, as you have your solution. PART may mean participant and users are not participants but conference hosts(?)

Bye, Olaf.

Christian de Bellefeuille

ASKER

It's just because you don't know the context. It's about conference call.
There's moderators, and participants. Both of them are persons, but only one of them pay for the service.
So we consider the moderator to be the real users, and participants are just some random people.

Olaf Doschke

Fine, all I'm saying is if you want answers, you shouldn't confuse, if you want answers, an easy way would be to talk of field1,2,3, so experts can simply concentrate on the technical case without being puzzled about meanings. On the other side many questions simply use 1:1 cases, and that's also working, if it doesn't mislead with the nomenclature or abbreviations.

I was about to post a group by PART, but thanks for taking the time and explaining that after the fact.

_agx_

Just to add my $0.02, it depends on the situation IMO. I agree that in this case field1,2,3 would be fine. However, I've also participated on threads where the use of generic names really obfuscated the issue. Descriptive names would have greatly reduced the confusion. So every situation is different.