Using operators in Access

[J1mmy]

I am using access, but someone might know how to apply this from SQL knowledge or otherwise.

I am trying to tell Access that if a value is between a certain range in a column, in another column, a figure needs to be calculated. For example, its working out postage price of an item based on weight.

I know I need to use the Between operator, but not sure how to construct it

[Old Pedant]

Be more specific: Do you need to make the calculation as part of the SELECT? Or as part of the WHERE clause?

For part of the SELECT, it might be something like this:

SELECT item,
price,
weight,
IIF(weight > 10, 4.95, IIF( weight > 5, 3.95, IIF (weight > 2, 2.95, 1.95 ) ) ) AS shipping
FROM table
WHERE ...

Notice that I did NOT use BETWEEN, because I didn't need to. But I could have:

SELECT item,
price,
weight,
IIF(weight > 10, 4.95, IIF( weight BETWEEN 5 AND 10, 5, 3.95, IIF (weight BETWEEN 2 AND 5, 2.95, 1.95 ) ) ) AS shipping
FROM table
WHERE ...

Is that kind of what you are looking for? If not, be more explicit.

[J1mmy]

Thanks for this.

I'm not too sure on the difference (I'm not all to familiar with Access)

However, as long as its provides the correct output, it will be fine.

I thought of another method. Maybe creating a new table, with the weight, and its corresponding price. Then implementing this as some kind of lookup query.

This should work, but is less efficient I suppose.

So in my Column called 'Weight (Grams)' I have a column next to it called Shipping Cost. When I input 35, the corresponding cell in the Shipping column should insert the price, lets say £2.

If we say that the weight between 1 gram and 200 grams is £2, how do I implement that in your expression?

Thanks

[Old Pedant]

[qupte]If we say that the weight between 1 gram and 200 grams is £2, how do I implement that in your expression?[/quote]

One problem with BETWEEN is that it is *INCLUSIVE*. So if we say BETWEEN 1 and 200 then *both* 1 and 200 are part of the included range. And if you have another range that is, say 200 to 500, then BETWEEN 200 AND 500 will *also* have 200 as part of the included range.

It's one reason that I tend to avoid BETWEEN for things like ranges.

It's usually better to say something like WHERE weight >= 1 AND weight < 200 so that 200 isn't part of the lower range.

************

[qupte]If we say that the weight between 1 gram and 200 grams is £2, how do I implement that in your expression?[/quote]

Using the shippingWeights extra table or not?

**********

creating a new table, with the weight, and its corresponding price. Then implementing this as some kind of lookup query. This should work, but is less efficient I suppose

No, I would say it is *MORE* efficient. That's the whole purpose of lookup tables.

So you would do something like this:

CREATE TABLE shippingCost (
low INT,
high INT,
cost CURRENCY )

And then you would join to it via something like:

SELECT P.item, P.price, P.weight, C.cost
FROM products AS P, shippingCost AS C
WHERE P.weight >= C.low AND P.weight < C.high
... etc. ...

ANd that's almost surely the best solution.

If nothing else, it means you can easily change the shipping costs by just changing the data in that table, and none of your coding has to change.

[J1mmy]

Well, regarding the BETWEEN issue, you can simply bump up the next range by one. So the next range will begin from, 201+. I've also just looked at the postal chart by the shipping company and that's what they do also - the next band starts from 201g. But yes, WHERE can work just as well. But you will have specify it like "< 201"

And how to implement it without the extra table - just leaving it until the expression to work out the shipping price?

But by using a look up query, won't I need to make some extra clicks to choose the right price? With an expression is completely automatic..

[Old Pedant]

regarding the BETWEEN issue, you can simply bump up the next range by one. So the next range will begin from, 201+.

And what happens if the weight is 200.5738 ???

Yes, you can do that, but then you have to ensure the weight is an integer, perhaps round or truncating it. I just find the >= and < to be less confusing. But it's clearly a personal choice.

But by using a look up query, won't I need to make some extra clicks to choose the right price?
Totally mystifies me. Extra clicks where??? on or in what? Lost me utterly.

*********

I showed you the way you can do it without an extra table in my first answer. What was wrong with that? Other than it's ugly coding and if prices or ranges change you have to go find and change your code, possibly in multiple places?

[J1mmy]

That's true if its something like 250.626525, but then again my scale only display grams with no decimal points. But point taken.

As for the tables, perhaps I don't fully understand this look up feature. For the sake of this thread, I'm creating a new table called Royal Mail Costs, and in it creating 2 columns: one called Weight (1g - 2000g), and the other called Cost. So as you can imagine, somehow, Access is suppose to look up the weight and match it with a price.
Is that roughly how I would design my tables?

I'm a bit confused as to where I'm suppose to put in your expressions if I was going to take the new table route. Are we taking about the same lookup feature? 'Modify Lookups in the table design view?

[Old Pedant]

Read my first post again: You need *TWO* weights in each record in you RoyalMailCosts table (and please don't put spaces or non alphanumeric characters into table and field names...excepting underline...it just makes later coding more confusing). You need the low and high weight of the range. It *can* be done with only a single weight field, but you have to then join to the RoyalMailCosts table twice and it really complicates the query.

And *NEVER* store TWO delimited values (such as the "1g - 2000g" that you showed!) into a single field in a table. That makes the coding even more difficult and prevents many useful SQL techniques.

**********

Since you never showed me your basic SQL query, I can't tell you exactly what to change.

But I showed you my version of the ShippingCost table (what you are calling RoyalMailCosts) and showed you the basic JOIN you would need to use all in my post # 4, above.

No, I do *NOT* think Access is smart enough to be able to automatically use a range-based lookup table like this. You will have to find the basic query that is displaying the results and go modify it to use a JOIN such as I showed in post # 4. You almost surely can't do it by click and shoot.

[J1mmy]

OK, lets go back to basics so I can implement something into Access. This Lookup method is sorta beyond my understanding of Access. It'd be easier for me to implement a expression. If that doesn't work, I'll take the lookup route.

In Access, I have the Expression Builder box open. It's sitting in the field for ShippingCost. Now, what EXACTLY do I need to put in this box to make it tell me the price based on this: 1-200g = £2.00 (We'll use the WHERE feature)
(I will be able to work out the expression after this)

[Old Pedant]

No, you won't use WHERE.

You can't use WHERE unless you do use a lookup table.

You will have to use what I showed in my first post.


IIF( weight BETWEEN 0 AND 200, 2.00, IIF( weight BETWEEN 201 AND 500, 3.50, 5.00 ) )

That reads like this:


If the weight is between 0 and 200 then the shippingCost is 2.00
Otherwise, if the weight is between 201 and 500 then the shippingCost if 3.50
Otherwise [i.e., if the weight is more than 500] the shippingCost is 5.00

I used 0 instead of 1 just in case somebody had a weight of 0.4 and it got rounded to zero.

Weight should be a simple number; it should *NOT* be a TEXT field with a "g" on the end.

If you really want the £ sign as part of the shippingCost, then you will have to use strings for the cost, thus:

IIF( weight BETWEEN 0 AND 200, '£2.00', IIF( weight BETWEEN 201 AND 500, '£3.50', '£5.00' ) )


If you don't know how to create SQL statements and use them in Access, then you are limiting severely what you can do. Access isn't the smartest product on the market, and its point and click capabilities are pretty limited. You have to be able to go "behind the scenes" to get real power from it.

[Old Pedant]

If weight is *NOT* an INTeger data type in your table, then for safety you probably should ensure that it is in the query:

[code]
IIF( CINT(weight) BETWEEN 0 AND 200, 2.00, IIF( CINT(weight) BETWEEN 201 AND 500, 3.50, 5.00 ) )

Even if it is an integer, adding CINT() won't hurt.

[J1mmy]

I suppose it can get a bit messy if I introduce an additional 10 bands of weight and prices?

Also, Access results in 'Expression not supported for conversion' - I attempted to copy and paste it

[Old Pedant]

??? No idea that conversion it could be referring to. Maybe CINT? That's a conversion. Stands for "Convert to INTeger". But if weight is any kind of number, it should be fine.

Ahhh...wait! *DO* your weight values include the "g" on the end? Such as "30g"?

*THEN* CINT("30g") indeed might be invalid!

[Old Pedant]

Yep, I just tested it. CINT('300g') chokes.

So if you aren't using pure number for weight, you'll have to get even more complex.


IIF( CINT(LEFT(weight,LEN(weight)-1)) BETWEEN 0 AND 200,
2.00, IIF( CINT(LEFT(weight,LEN(weight)-1)) BETWEEN 201 AND 500, 3.50, 5.00 ) )

Ehhh...maybe it will work okay if you just omit the CINT calls.

IIF( weight BETWEEN 0 AND 200, 2.00, IIF( weight BETWEEN 201 AND 500, 3.50, 5.00 ) )

[J1mmy]

Nope I simply copied and pasted it. No 'g', or even '£'. I'll try with your last one.

[J1mmy]

I entered this (Note the square brackets to indicate column)

IIf([Weight] Between 0 And 200,2,IIf([Weight] Between 201 And 500,3.5,5))

Same problem. But then I found this link, it'll probably mean more to you..
http://technet.microsoft.com/en-us/library/ms165758.aspx and http://msdn.microsoft.com/en-us/library/ms191530.aspx

[Old Pedant]

Those are both references to SQL Server, not to Access. Are you actually using SQL Server tables but reaching through Access? If not, those are irrelevant.

I'm afraid I'm lost at this point. I have no idea why that code won't work.