Query: Highest Count to Lowest Count

  • Thread starter Thread starter Xenophobe
  • Start date Start date
X

Xenophobe

My query looks like this:

SELECT ProductID, ProductName, ProductDivision
FROM Products

I need to group the results by division, from the highest number of products
to the lowest.

For example, table data looks like this:

ProductID ProductName ProductDivision

1 Product1 Division1
2 Product2 Division2
3 Product3 Division3
4 Product4 Division2
5 Product5 Division3
6 Product6 Division2
7 Product7 Division3
8 Product8 Division1
9 Product9 Division3

I need the recordset to return like this:

ProductID ProductName ProductDivision

3 Product3 Division3
5 Product5 Division3
7 Product7 Division3
9 Product9 Division3
2 Product2 Division2
4 Product4 Division2
6 Product6 Division2
1 Product1 Division1
8 Product8 Division1

The net result being:

Division3 has 4 records
Division2 has 3 records
Division1 has 2 records

Any suggestions would be appreciated.
 
1. Open your query in design view.

2. Depress the Totals buttron on the toolbar (upper sigma icon).
Access adds a Total row to the grid.

3. In the Total row under ProductID, choose Count.
In the Sorting row, choose Descending.

4. In the Total row under ProductDivision, accept Group By.

5. Remove ProductName from the grid.

The query now groups by ProductDivision, and gives a count of the rows for
that product, in descending order of the count.
 
Allen,

Thanks for your response.

This worked great, although not exactly what I was looking for. I will
probably need to do a separate query to get everything.
 
Back
Top