<div>
<br />
SELECT a.Fruit, (Select Count(b.Fruit) FROM myTable AS b WHERE b.Fruit=a.fruit ) AS Ctr<br />
FROM myTable AS a<br />
GROUP BY a.Fruit;
</div>



SELECT a.Fruit, (Select Count(b.Fruit) FROM myTable AS b WHERE b.Fruit=a.fruit ) AS Ctr
FROM myTable AS a
GROUP BY a.Fruit;

<div>
<div class="content wysiwyg-content">
In Access 2003, I sort my table by product count such as 100 apple, 50 bananas, etc, I want to list my products by the most stock on hand first, I want to add a field that will count the rows. When I add an autonumber field and make it the primary key field, it undoes my product count field sort and makes the products with the least stock on hand first, rather than the way I had it. Is there some way to count the rows w/o undoing my sort?
</div>
</div>


In Access 2003, I sort my table by product count such as 100 apple, 50 bananas, etc, I want to list my products by the most stock on hand first, I want to add a field that will count the rows. When I add an autonumber field and make it the primary key field, it undoes my product count field sort and makes the products with the least stock on hand first, rather than the way I had it. Is there some way to count the rows w/o undoing my sort?

Autonumber undoes sort in Access 2003 table

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.