• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

Autonumber undoes sort in Access 2003 table

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?
0
contrain
Asked:
contrain
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
SELECT a.Fruit, (Select Count(b.Fruit) FROM myTable AS b WHERE b.Fruit=a.fruit ) AS Ctr
FROM myTable AS a;
0
 
Rey Obrero (Capricorn1)Commented:

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;
0
 
contrainAuthor Commented:
Thanks,
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now