40 IIf statements efficiently??

[abrown]

I have a table with item # and retail price. The retail prices range from $0.00 to $400.00

I would like to create a third column called Retail range. Retail range is in increments of $5. For example:

0-4.99
5-9.99
10-14.99
15-19.99
etc all the way to 400

Can someone suggest the best way to accomplish this task??? IIf's, queries, combo boxes???

 

[ChrisUK]

Try a select case statement if this is indeed what you realy want to do.

It is inefficient to store data more than one in a DB . If you have the cost you don't need to store a range separately in a column of the same table all you ned to do is use a range in the query which you could construct using the select case statement

HTH

 

[SydneyGeek]

Which version of Access do you use? In XP and higher (not sure about 2000), you can use a Pivot Table view to group the items by increments of (or whatever interval you like).

 

[SydneyGeek]

You could enter this expression in a query:

Price Band: (IIf(Round([Retail Price]/5,1)-Int([Retail Price]/5)<0.5,Round([Retail Price]/5,0)+1,Round([Retail Price]/5,0)))*5

This will give the upper limit of each price band. To group on some other interval, change the 5's to your desired interval.

Denis

 

[ChrisUK]

Thats a really nice feature, i didn't realise you could do it. Thanks

 

[PaulF]

Hi

I agree that Denis' is a neat solution. An alternative is to use the partition function in your query. The microsoft knowledge base has an article

http://support.microsoft.com/default.aspx?scid=kb;en-us;208556

This works well if the intervals are constant.

 

[abrown]

Thanks for all of the advice. We tried the IIF in the query that gives you an upper limit and it worked great. BUT the Price column contains some text values so there are #Error messages in the column.

Also what we are really looking for the formula to give us a range and not just the upper limit. So if the price is 14.00 it would give us "10-15" in the price range column.
Is there any way to do this in one formula?

 

[PaulF]

Did you check out the partition function? Use the link above. It is easy and will, I believe, do exactly as you want.

If this gives you a problem post back.

 

[PaulF]

OK: In a new field- Retail_Range:Partition([Retail Price],0,400,5).

This should work.

 

[abrown]

The partition formula worked great. Thanks.

The other challenge is that when the price is 5.99 the formula returns the range 6:10. I want 5.99 to fall into 5:9.99 instead.

How can I make the range recognize 2 decimal points? I would like the ranges to layout as follows:

0-4.99
5-9.99
10-14.99
15-19.99
etc all the way to 400