Help with Query Switch Function

[cannonej76]

I am trying to segment customer into ranges of amounts purchased. When I use the switch function, Access tells me it is too complex. I believe there is a limit of 7 and I need 18.

Here was my formula that was too complex.

2011_Status: Switch([2011] = 0,1,[2011] Between 0 And 5000,2,[2011] Between 5000 And 10000,3,[2011] Between 10000 And 150000,4,[2011] Between 15000 And 25000,5,[2011] Between 25000 And 50000,6,[2011] Between 50000 And 100000,7,[2011] Between 100000 And 200000,8,[2011] Between 200000 And 300000,9,[2011] Between 300000 And 400000,10,[2011] Between 400000 And 500000,11,[2011] Between 500000 And 600000,12,[2011] Between 600000 And 700000,13,[2011] Between 700000 And 800000,14,[2011] Between 800000 And 900000,15,[2011] Between 900000 And 1000000,16,[2011] Between 1000000 And 2500000,17, [2011]>2500000,18)

Here are the categories.

$0k-$5k$5k-$10k$10k-$15k$15k-$25k$25k-$50k$50k-$100k$100k-$200k$200k-$300k$300k-$400k$400k-$500k$500k-$600k$600k-$700k$700k-$800k$800k-$900k$900k-$1000k$1000k-$2500k

Any ideas?

 

[boblarson]

When you get that many, just build a function. Not only will it simplify things but it will most likely work faster than it trying to go through that type of horrendous Switch.

 

[cannonej76]

thank for the reply... I am not familiar with writing functions... any help is greatly appreciated!

 

[boblarson]

Sorry, now that I read it again I am thinking that you may not need a function nor the switch function. What value are you looking for? Are you looking for the sum of the amounts in a field named 2011? (not a good name for a field if it is by the way)

If so, you should be able to use these in your query:

0to5K:Sum(IIf(Nz([2011],0)<=5000, Nz([2011],0),0)

5Kto10K:Sum(IIf(Nz([2011],0)>5000 And Nz([2011],0) <=10000, Nz([2011],0),0)

And so on as different fields in the query. Then you will have your breakdown. If ALL of the ranges would be the same exact number in between, so like 10,000 intervals, you can use a function named Partition which will give you the breakdowns automatically.
O

 

[cannonej76]

I went about it from a different angle and it works perfectly. Thank you for mentioning Partition in your response. I searched for some articles on Partition and Frequency Distribution.

I thought about and started from scratch.

A combination of IIF and Partition does the trick.

Range: IIf(([AMOUNT] Between 0 And 25000),Partition([AMOUNT],0,25000,5000),IIf(([AMOUNT] Between 25000 And 50000),Partition([AMOUNT],0,50000,25000),IIf(([AMOUNT] Between 50000 And 100000),Partition([AMOUNT],0,100000,50000),IIf(([AMOUNT] Between 100000 And 1000000),Partition([AMOUNT],0,1000000,100000),IIf(([AMOUNT]>=1000000),"1000000+",0)))))

Sample Results:

RegionCustomer_NumberYearAmountRangeWest508932011 $ 29,455 25000:49999East 8746072011 $ 5,974 5000: 9999West506742011 $ 60,905 50000: 99999West206842010 $ 131,801 100000: 199999South581702011 $ 1,214 0: 4999South529762011 $ 14,452 10000:14999West922952011 $ 17,980 15000:19999South512802011 $ 24,180 20000:24999West182862011 $ 331,632 300000: 399999South9776882011 $ 463,081 400000: 499999South672952010 $ 1,006,165 1000000+South1283742010 $ 514,108 500000: 599999


Thank you very much!!