Selecting record if value is between a and b

Thread: Selecting record if value is between a and b

1. Member
Join Date
Dec 1969
Posts
86

Selecting record if value is between a and b

I have a table of shipping costs where each weight has a cost against it.<BR><BR>I need to be able to input the total weight of a parcel and then return just the one price from the access table. This works fine if the weight exists but the table goes up unevenly eg<BR><BR>0.1kg - 2.00<BR>0.3kg - 2.99<BR>1.2kg - 5.75<BR>10kg - 12.45<BR><BR>If the weight doesn&#039;t exist, then I need to get the next weight after it, eg, if the total weight of the parcel is 4kg, then I need to get the price for the 10kg parcel.<BR><BR>I have no idea how to do this. I&#039;ve had a look through the FAQs and done a search in Google but not come up with anything. Is it is possible?<BR><BR>Thanx

2. Senior Member
Join Date
Dec 1969
Posts
1,843

RE: Selecting record if value is between a and b

I would do it like this, but there may be a better way... <BR><BR>Select Top 1 Shipping_Cost From MyTable Where Weight &#062;= "&#039; & Package_Weight_Variable & &#039;" Order By Shipping_Cost<BR>

3. Senior Member
Join Date
Dec 1969
Posts
273

RE: Selecting record if value is between a and b

It shouldn&#039;t be a problem...<BR><BR>select top 1 price<BR>from Table<BR>where weight &#062;= 4<BR>order by weight<BR><BR>Gavin

4. Senior Member
Join Date
Dec 1969
Posts
1,843

Another small suggestion

If I were you, I would seperate the Weight Number and teh Weight Unit into two fields, like this: <BR><BR>Weight &#124 Unit &#124 Price<BR>---------------------<BR>0.1 kg 2.00<BR>0.3 kg 2.99<BR>etc...<BR><BR>this will give you more flexabiltiy to do conversions and other manipulation in the future. Just my \$.02

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•