Help for building a statistical query, thanks a lot!

[xpengi]

Hi,

I want build a SQL query to generate statistical result from a table(students) in MS Access as below:

Student# City Amount
001 toronto 1000
002 ottawa 3000
003 toronto 4000
004 ottawa 8000
005 montreal 11000
006 vancouver 15000

i build a query but does not work:

SELECT Amount AS ["0~5000"], COUNT(1) AS ["NUMBER OF STUDENTS"]
FROM students
WHERE amount > -5000

UNION SELECT amount AS ["5000~10000"], COUNT(1) AS ["NUMBER OF STUDENTS"]
FROM students
WHERE amount < -5000 AND amount > -10000;

i want to get result of how many students who have loan amount is between 0-5000, 5000-10000, 10000-15000?

output:

Amount***************** Number of students
0-5000***************** 3
5000-10000***************** 1
10000-15000***************** 2


how to do that?

Thanks a lot.

Peter