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?
LVL 10
Christian de BellefeuilleProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.