Christian de Bellefeuille
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.
Anyone can help me?
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
Anyone can help me?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Bye, Olaf.
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.
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.
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.
I was about to post a group by PART, but thanks for taking the time and explaining that after the fact.
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.
ASKER
@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.