I have a query with 5 fields. One field is called ICID. There may be 100,000 records and I want a query that will display the TOP 25 ICID's that show up the most. I'm trying to write the query but I'm doing something wrong and can't get it to work. Here is my query without the Count function:

SELECT CELL109.ICID, CELL109.SWITCH, CELL109.TRBLCODE, CELL109.DATE, CELL109.TIME
FROM CELL109
GROUP BY CELL109.ICID, CELL109.SWITCH, CELL109.TRBLCODE, CELL109.DATE, CELL109.TIME
ORDER BY CELL109.ICID DESC;

How do I add the count function to get this to work. I've tried

SELECT CELL109.ICID, Count(CELL109.ICID) AS (CELL109.Number_of_Events), CELL109.SWITCH, CELL109.TRBLCODE, CELL109.DATE, CELL109.TIME
FROM CELL109
GROUP BY CELL109.ICID, CELL109.SWITCH, CELL109.TRBLCODE, CELL109.DATE, CELL109.TIME
ORDER BY Count(CELL109.Number_of_Events) DESC;

This does not work. I've looked through the archives and I see method I'm supposed to use but I still can't get it to work. I hope someone can help me here. Thanks.

If you only want to summarize on the ICID field, do not include the other field in your summary query. Try something like:

SELECT TOP 25 CELL109.ICID
FROM CELL109
GROUP BY CELL109.ICID;

This will get you a list of 25 ICID values as a query result.

You can join this query to your detail table in another query if you want to build a form/subform to view the details per ICID, or whatever.

Don't include irrelevant fields (in this context) in a summary query.

I see how this works, but I need a Count field also that will display the number of occurences that each ICID has in the table. This is where I was running into trouble. Getting this to work is what is giving me errors. Thanks.

As an example what I want is something like this. If there are 100,000 records in a table and in the ICID field there are 50 unique 'ICIDs'. I want to have a query that will find the top 25 ICID's that show up the most in the table.

Query result should look something like this:

ICID NumberOfEvents Switch Date Time
10Y 22222 x x x
39Z 20000 x y y
.
.
5X 392 x z z

Thanks again.

[This message has been edited by NJudson (edited 02-22-2002).]

SELECT TOP 25 table.ICID, Count(table.SomeField) AS CountOfSomeField
FROM table
GROUP BY table.ICID
ORDER BY Count(table.SomeField) DESC;

This actually was faster to do in Design View:
Show Table
ICID field, SomeField
Sigma button
Count: SomeField, Sort Descending
Query Properties> Top Values: 25

HTH,
David R

Thanks David. I finally got working. I see now what KKilfoil was saying on not using other fields on a summary query.

[This message has been edited by NJudson (edited 02-22-2002).]