How to create a query/table in MS-Access where you count customers in certain capital brackets?

I am at wits end, partly because I don't know which keywords I need to Google this? I hope you could help me do this in either SQL or MS Access Query Design.

So there's a question I need to solve, which is "How many customers do we have with a capital between $0-500K, $500k - $1mln and >$1mln?"

I have this kind of table after selecting the relevant columns:

Customer_ID | Capital_in_USD
0000001     | 345678
0000002     | 97602
0000003     | 765008
0000004     | 1342345
etc..

I expect to query a table which looks like this, where 1= $0-500K, 2= $500k - $1mln and 3= >$1mln :

Bracket | Count
1       | 2
2       | 1
3       | 1

Is this achievable in MS Access? I've been told I need 3 formulas.

Solution

Use aggregation. I would recommend using strings, instead of numbers, so:

select switch(Capital_in_USD < 500000, "$0-500k",
              Capital_in_USD < 1000000, "$500k-$1M",
              1=1, "$1M+"
             ), count(*)
from t
group by switch(Capital_in_USD < 500000, "$0-500k",
                Capital_in_USD < 1000000, "$500k-$1M",
                1=1, "$1M+"
               )

Of course, you can use numbers rather than strings.