• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

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?
0
Christian de Bellefeuille
Asked:
Christian de Bellefeuille
  • 3
  • 2
  • 2
2 Solutions
 
_agx_Commented:
I don't use Access, so I'm not sure if it supports this, but try:

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

Open in new window


If that isn't supported, try a subquery:

SELECT t.User, Count(t.Conf) AS TotalConf
FROM (SELECT DISTINCT User, Conf FROM MyTable WHERE <some date range>) as t
GROUP BY t.User

Open in new window


If that doesn't work, my Access bag of tricks is empty, so I'll leave it to someone else to answer  ;-)
0
 
Olaf DoschkeSoftware DeveloperCommented:
Are your captions correct? Then I wonder why usernames are in a column named PART and there are several usernames for the same user number.

Bye, Olaf.
0
 
Christian de BellefeuilleProgrammerAuthor Commented:
@_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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
Christian de BellefeuilleProgrammerAuthor Commented:
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.
1
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
_agx_Commented:
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.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now