how to create a count in a group

  • Thread starter Thread starter LG
  • Start date Start date
L

LG

I have a table of 308,000 records. How do I find what the top 20 outcomes
would be? For example there are different codes 1-86 there maybe 50 #80's, 2
#1, 160 #19. I need to pull the top 20 that occur the most.+2nd would be
there are several types of claims and I need to find the top 20 that were
sent in. How do I go about setting up the query there is only 1 table built
since it is a data transfer.
 
SELECT *
FROM YourTable
WHERE Codes in
(SELECT TOP 20 Codes
FROM YourTable
GROUP BY Codes
ORDER BY Count(Codes) DESC)

In you existing query, you would need to put something like the following in
the criteria under codes.

Field: Codes
Criteria: IN ((SELECT TOP 20 Codes FROM YourTable GROUP BY Codes ORDER BY
Count(Codes) DESC)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I am not sure where to go with this. I have the table and query with the
data I need. Where would I put the below?

KARL DEWEY said:
Totals query like this --
SELECT TOP 20 Codes, Count([Codes]) AS CountOfCodes
FROM YourTable
GROUP BY Codes
ORDER BY Count([Codes]) DESC;

LG said:
I have a table of 308,000 records. How do I find what the top 20 outcomes
would be? For example there are different codes 1-86 there maybe 50 #80's, 2
#1, 160 #19. I need to pull the top 20 that occur the most.+2nd would be
there are several types of claims and I need to find the top 20 that were
sent in. How do I go about setting up the query there is only 1 table built
since it is a data transfer.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top