Need help to make a query

[fabilewk]

Hi, I'm facing the following problem:
I have a table (called stats) which has 2 text fields: "user" and "document" (among others). In this table we keep the accesses to certain documents.
For example:
user document


usrA docA
usrA docA
usrA docA
usrA docB
usrA docC
usrB docB
usrB docC
usrC docA
usrC docA
usrC docA

I need the output to the query to be:

user accessed docs total
usrA 3
usrB 2
usrC 1

I need a ranking of users ordered by distinct document accessed amount.
We need to know who are the users who accessed to more documents.
As far as I know, MS Access do not accept the COUNT(DISTINCT...) clause.
Any ideas? I appreciate any help.
Thanks in advance,

fabilewk

P.S. Excuse me for my poor English

[June7]

Your output data sample does not reconcile with the input.

Should it be like:
user accessed docs total
usrA 5
usrB 2
usrC 3

This is a simple GROUP BY query:
SELECT User, Count(Document) AS CountOfDocument
FROM Stats
GROUP BY User;

[fabilewk]

Thanks for your interest.
I need the count of distinct documents. So, If the user usrA read 3 times the same document (docA) the output should display
usrA 1

[June7]

Understand better now, try:
SELECT User, Count(User) AS CountOfUser
FROM (SELECT User, First(Document) AS FirstOfDocument
FROM Stats
GROUP BY User, Document) As Query1
GROUP BY User;

[fabilewk]

It worked fine.
Thanks a lot!

[fabilewk]

Hello, users need now the query to display just the first 20 users, so I added TOP 20 following the select clause. The problem is that the query is displaying more than 20 records. Do you know why?
Thanks again!

SELECT TOP 20 Stats.[userid] AS User, Count(Stats.[userid]) AS Docs
FROM (SELECT Stats.[userid], First(Stats.[document]) AS FirstOfDocument FROM Stats WHERE GROUP BY Stats.[userid], Stats.[document]) AS Query1
GROUP BY Stats.[userid]
ORDER BY Count(Stats.[userid]) DESC;