Postage table SQL query required
Posted on 2006-06-07
I have created a table in MS Access:
pcID WeightKG1 WeightKG2 Courier Price
1 0 1 Royal Mail £3.95
2 1 2 Royal Mail £5.95
3 2 5 Parcelforce 48 Hour £7.95
4 5 25 Parcelforce 24 hour service £9.95
5 25 1000 Parcelforce 24 hour service £0.00
A user can specify a total weight in a form, then an SQL query needs to select the correct courier above, by determining if the weight is between the 'WeightKG1' and 'WeightKG2' column values.
For example, if a user specified 4KG, then the query should return 'Parcelforce 48 Hour'. As 4KG falls between the band for row id 3 (pcID).
1) What is the SQL to determine the correct courier depending on the weight a user specified? I may need to change the table structure, if so, to what?
Thanks in advance,