Bucket ranges 1

[Noah114]

I have a database that is built for prevalence modeling; My main driver is, if a new client comes on that has 60,000 customers, what can we expect for sales.

I have historical data for 1,500 clients all at different sizes. I would like to bucket client size ranges. For example, if the client is between 20,000 and 30,000 bucket as 25,000. If the client is between 30,001 and 40,000 bucket as 35,000. I tried building an IIF statement but it became to big because my buckets start at 25,000 and would go to 1,000,000.

Is their a module or language that can be built to bucket these instead of if statements or reference tables?

 

[SgtJarrow]

Instead of IIF, take a look at the SELECT CASE statement. It is much each to read and maintain. It can be created inside a function, and you could call that function anytime you are trying to determine which bucket to place the client in. Something like:

Public Function GetBucket(dblClientSize As Double) As Double
    Select Case dblClientSize
        Case 20000 To 30000: GetBucket = 25000
        Case 30001 To 40000: GetBucket = 35000
    End Select
End Public

The sizes listed in the Case lines don't have to be equal...so if you have buckets after 100K that go from 100K to 200K, you can use that. Just read the help file on SELECT CASE.

That should get you started.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[MajP]

The select case proposed by mstrmage1768 would give you a lot of flexibility to have different size bins. If you wanted all 10k buckets you could build a simple function. I think this works for all cases.

Public Function TenKBucket(dblValue As Double) As Long
  TenKBucket = (Fix((dblValue - 1) / 10000) * 10000) + 5000
End Function

 

[CautionMP]

Noah114,
I was thinking of something along the same lines as MajP, and the math is simple enough you could just use a calculated field in the query without any VBA.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[SgtJarrow]

Nice majp...have a star!

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer