order by amount of times something appears in a table 1

[mattpont]

Hi,

I would like to order a recordset/query result by the amount of times something appears in a table.

For example, the table holds records of downloads. I would like to be able to pull from this table a list of people who have downloaded the most stuff. (i.e. how many times each person appears in the table)

What is the best way of going about this please?

Thanks

 

[lth]

There's a kind of query called a summary query - if you run Access's Query Wizard you should be able to figure out what to do from there, or you can just push the totals button in the query designer (the greek sigma symbol) and select 'sum' for the 'amount downloaded' field.

Cheers

K

 

[mattpont]

Thanks for your help, but it doesn't seem to do what I wanted.

This just seems to return the count for all the records in the database, whereas I want a count for each distinct person.

For example, if the results go something like this:
John,
John,
Mark,
John,
Sally,
Tony,
Mark,
Sally.

I want the results to show:
John, 3.
Mark, 2.
Tony, 2.
Sally, 1.

Is this possible?

 

[r937]

select person, count(*)
from yourtable
group by person
order by count(*) desc


rudy

 

[mattpont]

Worked like a charm.

Thanks very much for your help.