Number ranges

[Kenny Robb]

I have a query that produces a big long list of account numbers and
anounts.

I would like to be able to write a query that can tell me the number
of accounts within a range of the amount in the account.

For example

0-100$ 4
100 - 200$ 6
200-300$ 10

etc etc......I have about 20 of these ranges.

Thanks
Kenny

 

[Guest]

Kenny,

This is one way to do it.
Create a table with 3 fields (ID, MIN_NUM, and MAX_NUM).
The data will be something like this:
ID MIN_NUM MAX_NUM
------- ---------------- ---------------
0-100 0 100
101-200 101 200
201-1000 201 1000
2000-10000 2000 10000
.. . .

Then join your table with the account numbers and amounts to this table.
Your table with account numbers and amounts is Table1.
The new table with ID, MIN_NUM, and MAX_NUM is Table2.

Query_Count: Something like this:
SELECT Table1.Account, Table1.Amount, (select ID from Table2 where [Amount]

= MIN_NUM and Amount <= MAX_NUM) AS ID

FROM Table1;

Once you have the query, all you have to do is to do a summary query to get
the count you need.

Like this:
SELECT Query_Count.ID, Count(Query_Count.ID) AS CountOfID
FROM Query_Count
GROUP BY Query_Count.ID;

Hope this works for you.