Counting multiple values in a field 1

[Yukonbanzai]

Trying to count specific values in a field.
Table has field named "rank" with 8 possible text values, (Military Ranks).
How can I count how many of each value.
I need to be able to create a report that states how many Sgt's, Cpl's, etc.

 

[SonOfEmidec1100]

Something like this should do it.

SELECT Rank,  COUNT(Rank) AS NumberInRank FROM MyTable GROUP BY Rank ORDER BY Rank

 

[Yukonbanzai]

Works perfect

Thanks

 

[Yukonbanzai]

Is there a way to expand this to take in ranks from two tables?
I have a table with the NCO ranks and a different table that has the officer ranks.
Tables are "Cadet" and "Adult". Both have a field named "Rank".
The end result will be a parade state for the unit which needs to list how many of each rank there are.

 

[SonOfEmidec1100]

Ok try this

SELECT Rank,  COUNT(Rank) AS NumberInRank FROM Cadet GROUP BY Rank
UNION ALL
SELECT Rank,  COUNT(Rank) AS NumberInRank FROM Adult GROUP BY Rank 
ORDER BY Rank
[code/]

 

[Yukonbanzai]

Thanks again
Works just the way I need.