Banding data

[leebo1973]

morning all

I have a list of values that I would like to put easily into bands like

0-100
101-200
201-300

If there something easy I can do, and i have values ranging from 0 to 25,000

Thanks

 

[namliam]

Yes well, relatively easy ... a few options though....
Do you want "standard" 100 brackets like you listed or will it change starting perhaps at 100 but ending at 20000-25000?

Easiest and "most flexible" way is to make a table with 3 columns: StartNumber, EndNumber, BracketText

Then in a query simply add this table and your source table with numbers. DO NOT add a relationship between the tables, instead
Where Number >= StartNumber and Number < EndNumber

Another alternative may be to have a table that lists all 25000 numbers and their assigned brackets (thus only two columns) now you can make a simple join between the two tables, thoug its a bit harder to maintain if the brackets ever need changing.... it may be a little simpler to create

I hope this is clear enough?

 

[leebo1973]

Thanks

I dont understand where I am supposed to typr the bot about STartNumber>= etc???

 

[namliam]

You would do this as criteria.... You add your Number column and in the criteria simply add:
>= StartNumber and < EndNumber

The full where you can also copy/paste into the actual SQL if you know where to find that, but I doubt that if you are stuck on the "WHERE" clause.

 

[leebo1973]

Again, thanks

So I have a column called Project Fee, which has all the values I want to bracket. Have created the table with the 3 columns as suggested

How do I get the Bracket Result in the query??

 

[namliam]

1) Create a query
2) Add your source table
3) add your Bracket table
4) add columns including Project fee (name convention !! )
5) Add column "bracket" from the bracket table
6) In the criteria of the column Project fee, add:
>= [StartNumber] and < [EndNumber]

Where startnumber and endnumber are the columns from your table, I added the [] since you will likely need them